SQL smarts - can I identify all media with longest retention?

Last post 04-13-2013, 9:40 PM by SurfDad. 3 replies.
Sort Posts: Previous Next
  • SQL smarts - can I identify all media with longest retention?
    Posted: 03-27-2013, 12:14 AM

    Hi Everyone,


    I have been painstakingly providing a couple of our customers with media forecasts for the next twelve months, based on the usual suspects ..... tapes written to fulls, number of fulls required according to extended retention settings etc etc.


    One of many pain-in-the-*** tasks if weeding out tapes with the longest retention, usually seven years or even indefinite.


    The trouble I have, is that there is no easy way of identifying this, as 'extended retention' groups, weekly, monthly and half-yearly\yearly all together.


    I am not interested in weekly or monthly, coz those tapes return to scratch, but the seven year and infinite are effectively lost to the world of scratch tapes for good.


    As the reports can't help me on this (I have pored over them), and nothing in the GUI that I have seen can help either, I was wondering if anyone knew any SQL commands and on which tabels that could weed this information out.


    Once I can identify these tapes, I can exclude from numbers returning to scratch and finally provide an accurate figure on estimated tape shortfalls for the coming 12 months.

    I know that under the bonnet, all the fancy stuff can be acheived at SQL level ..... but I'm not familiar enough on how to code the SQL query myself.


    Help appreciated as usual ...an interesting issue indeed.





  • Re: SQL smarts - can I identify all media with longest retention?
    Posted: 04-11-2013, 1:12 PM

    You can use our Commcell Views to query the database.

    CommCell Views provides a way to query information on the CommCell components directly from the SQL database.


    You can use these default views, or you can create or customize the existing views to reflect the data in your organization. The views are created by querying the database. These query are by default displayed in SQL Enterprise Manager. You can also use products such as Crystal Reports, Microsoft Reporting Services and/or Microsoft Excel to format your query output.

    If you modify a view or create a new view, you must reapply them after each new release.

    Here is the 9.0 documentation link:


    Thank you,
    CommVault Customer Support
    24/7 Support Hotline#: 1.877.780.3077

  • Re: SQL smarts - can I identify all media with longest retention?
    Posted: 04-11-2013, 1:16 PM

    Here is a more specific link for you to the Retention Info view. I believe this is specific to what you are looking for.




    The CommCellRetentioInfo view provides information about each Retention information.

    The following image displays a sample CommCellRetention view:

    Column Description
    commcellid The unique ID for commcell.
    commcellname The commcell name.
    spname The storage policy name.
    copyname The storage policy copy name.
    retentiondays The number of retention days.
    cycles The number of cycles.
    Isdataagingenabled The Is data aging enabled on this copy.
    Ismanageddiskspaceenabled The disk space Is managed disk space enabled.

    Thank you,
    CommVault Customer Support
    24/7 Support Hotline#: 1.877.780.3077

  • Re: SQL smarts - can I identify all media with longest retention?
    Posted: 04-13-2013, 9:40 PM

    Thanks for the reply Jen.


    Although that is useful, it doesn't give me the association of resources per the Storage Policy.


    For example, I would like to see the number of tapes with the maximum  extended retention per Storage Policy so I can quickly exclude from returning to scratch anytime soon.


    However, I will look through more of the views and see if there is a way of cross-referencing a handful of tables to give me what I need.


    A good time to learn a bit of SQL .....



The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Copyright © 2020 Commvault | All Rights Reserved. | Legal | Privacy Policy