locked
how to check site collection size in content database for past 6 months RRS feed

  • Question

  • Hi there,

    we have a large sharepoint site collection with storing documents. 

    We want to check how its size in content database is growing for past 6 months?

    Could anyone please share any useful links to get the information.

    Thanks
    Wednesday, March 28, 2018 3:49 PM

All replies

  • Hello,

    there is no OOB feature that could show you the site collection growth rate for the past 6 months.

    You can isolate this site collection in a specific CDB and use SPHealthdbSize (a Codeplex solution).


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.

    Wednesday, March 28, 2018 4:14 PM
  • Hi AK SPAdmin,

    As Aleksandar have mentioned above , there is no OOB or sharepoint power-shell commands for tracking the content databases size history, but it is applicable by executing the Pure SQL Script , the script uses backup history to analysis the grow of the content db sizes.

    Here's The Full article with more explanation : Database size growth as a list

    Here's an excerpt about the script description 


    -- Database size growth as a list
    
    -- Transact-SQL script to analyse the database size growth using backup history.
    
    /*
    This Transact-SQL script uses the backup history to analyse the growth of the databases size over a given period.
    Additional to the minimul, maximum and average size per month the growth of average size related to the former month is calculated.
    The values are useful for future resource planning of the storage and backup system.
    
    Works with MS SQL Server 2005 and higher versions in all editions.
    Requires access and select permissions to the msdb system database.
    */
    
    -- Transact-SQL script to analyse the database size growth using backup history.
    DECLARE @endDate datetime, @months smallint;
    SET @endDate = GetDate();  -- Include in the statistic all backups from today
    SET @months = 6;           -- back to the last 6 months.
    
    ;WITH HIST AS
       (SELECT BS.database_name AS DatabaseName
              ,YEAR(BS.backup_start_date) * 100
               + MONTH(BS.backup_start_date) AS YearMonth
              ,CONVERT(numeric(10, 1), MIN(BF.file_size / 1048576.0)) AS MinSizeMB
              ,CONVERT(numeric(10, 1), MAX(BF.file_size / 1048576.0)) AS MaxSizeMB
              ,CONVERT(numeric(10, 1), AVG(BF.file_size / 1048576.0)) AS AvgSizeMB
        FROM msdb.dbo.backupset as BS
             INNER JOIN
             msdb.dbo.backupfile AS BF
                 ON BS.backup_set_id = BF.backup_set_id
        WHERE NOT BS.database_name IN
                  ('master', 'msdb', 'model', 'tempdb')
              AND BF.file_type = 'D'
              AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate
        GROUP BY BS.database_name
                ,YEAR(BS.backup_start_date)
                ,MONTH(BS.backup_start_date))
    SELECT MAIN.DatabaseName
          ,MAIN.YearMonth
          ,MAIN.MinSizeMB
          ,MAIN.MaxSizeMB
          ,MAIN.AvgSizeMB
          ,MAIN.AvgSizeMB 
           - (SELECT TOP 1 SUB.AvgSizeMB
              FROM HIST AS SUB
              WHERE SUB.DatabaseName = MAIN.DatabaseName
                    AND SUB.YearMonth < MAIN.YearMonth
              ORDER BY SUB.YearMonth DESC) AS GrowthMB
    FROM HIST AS MAIN
    ORDER BY MAIN.DatabaseName
            ,MAIN.YearMonth
    Hope this will help you


    Best Regrads, Ahmed Madany MCTS @twitter http://twitter.com/ahmed_madany @Blog http://ahmedmadany.wordpress.com @LinkedIn http://eg.linkedin.com/pub/ahmed-madany/35/80/2b6

    • Proposed as answer by Anthony_1 Thursday, March 29, 2018 6:36 AM
    Wednesday, March 28, 2018 4:54 PM
  • Hi AK SPAdmin ,

    There is no OOB feature like your request , but you can reference here to deploy a custom solution .

    Best Regards ,

    Anthony


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, April 5, 2018 8:06 AM
  • Hi AK SPAdmin,

    did you get away for your requirements ? please let us know if you've fixed it or still need any assistance

    Thanks,

    Ahmed


    Best Regrads, Ahmed Madany MCTS @twitter http://twitter.com/ahmed_madany @Blog http://ahmedmadany.wordpress.com @LinkedIn http://eg.linkedin.com/pub/ahmed-madany/35/80/2b6

    Monday, April 9, 2018 12:54 PM