none
Last 6 month Database Growth RRS feed

  • Question

  • Hello,

    I am trying to find it out to get the last 6 month Database Growth using Backup size but having issue as we have job purging the Backup History table.

    Whenever i run it i am just getting current month backup size.

    Is it any other way we can get it previous 6 month Database growth?

    Thanks in advance for your help!

    Friday, May 22, 2020 9:24 PM

All replies

  • If you have not been recording the database size somewhere, your only options is to go the vaults and dig up the backups from six months ago.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 22, 2020 9:41 PM
  • Thanks Erland for prompt response.

    We are not storing Database size but i will be creating DBGrowthTrack Table bit meanwhile i need previous few months records so i can get the some estimation how the Data Growth happening which also will be helpful for me for Capacity planning.

    Any other thing which you can point it out like Database File size or Disk size?

    We use 3rd party software so how i can query to get it?

    Friday, May 22, 2020 10:15 PM
  • Hello,

    The info you searching for is saved on backupset table of msdb database.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-ver15

    If you purge that you cant find backup history and growth.

    Only chance if you have kept these backups, you can perform header only restore to check the size then.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql?view=sql-server-ver15

    Friday, May 22, 2020 10:15 PM
  • We don't have access as normally when we need to  we have to ask them copy stream files somewhere else.

    Normally i have to query like for each backup sets means for last 6 month then daily full backup 30 days * 6 times?

    RESTORE HEADERONLY FROM DISK = N'\\DailySqlDb-FullBackup.bak';

    Unfortunately we have job set up before i start and it's purging so whenever i query i can get only for last few days.

    Friday, May 22, 2020 10:32 PM
  • we have job purging the Backup History table.

    You already answered your question: You delete the required information from history, so you can't get the information.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, May 23, 2020 3:50 AM
    Moderator
  • Thanks Olaf.

    You are right that's why i am looking any other way if someone had tips.

    Saturday, May 23, 2020 2:38 PM
  • In addition, if you have a consequence backup of the database you may get some idea for the backup size but if you want precisely size , as Eland pointed start recording... See an example


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 24, 2020 5:05 AM
    Answerer
  • The below system views on msdb system database are primarily associated for storing information of DB Backups information 

    dbo.backupset: provides information concerning the most-granular details of the backup process
    dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets
    dbo.backupfile: this system view provides the most-granular information for the physical backup files

    If these tables are purged, do you have old backups of MSDB database. If yes you may retrieve the backups for old months (preferably the month end backup) and restore them temporarily. You can run a query to check for the backup size as below

    USE [DBNAME]
    GO
    SELECT 
    x.database_name as Database_Name,
    y.physical_device_name as Phy_Name,
    CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
    x.backup_start_date,
    CASE x.[type] WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
    WHEN 'L' THEN 'Transaction Log'
    END AS BackupType,
    x.recovery_model
    FROM msdb.dbo.backupset x
    INNER JOIN msdb.dbo.backupmediafamily y ON x.media_set_id = y.media_set_id
    ORDER BY backup_start_date DESC, backup_finish_date
    GO
    • Proposed as answer by Jayakrishna J Sunday, May 24, 2020 8:48 PM
    • Unproposed as answer by Jayakrishna J Monday, May 25, 2020 4:30 AM
    • Proposed as answer by Jayakrishna J Monday, May 25, 2020 4:30 AM
    Sunday, May 24, 2020 8:46 PM
  • Hi pdsqsql,

    If the backup history information in the msdb database has been cleared, it is may not feasible to check the growth of a particular database by viewing the backup records.

    But since you have daily full backup for the past six months, you can also consider using each month instead of daily as a time node to obtain database growth records.

    Also check this:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7d82aaf-bf44-40fb-b5c3-d4bcb54a54a5/get-last-6-months-data-file-growth-on-each-database?forum=transactsql

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 25, 2020 2:54 AM
  • Thanks JayaKrishna.

    Appreciate for providing the query.  I am also running this kind of query to get backup details, but the problem having i can go back to certain days as we are purging the backup history.

    Old backups of MSDB database, i have to restore either end of each month backup and then see but issue having i have to go through different channels and ask them to provide me the stream files and then restore it if i can get it.

    Do you think any other way i can get it using Database file size or Disk space?

    Monday, May 25, 2020 4:14 PM
  • Thanks Cris for providing your view and url.

    Not sure i can get it last six month backup or not for the MSDB backup, it's little harder as need to go different channels and also we have lots of servers so if i need it then i will have to restore all the MSDB for different servers in to another server and then query the MSDB.

    Monday, May 25, 2020 4:18 PM
  • Thanks Uri.

    I will planning to store the data so at least i can have for next few months.

    What will be the best to store Database size or Backup size?

    Monday, May 25, 2020 4:19 PM
  • not everyone does msdb maintenance. The more important part is to keep the job history table size reasonable, particularly if you run some job very frequently. I have actually re-written the sp_sqlagent_log_jobhistory procedue for flushing job-history as it is very bad for frequently run jobs - specifically to not flush on every occurrence. see 

    http://sqlblog.com/blogs/joe_chang/archive/2013/03/05/job-history-row-limiter.aspx

    I suggest keeping the record of full backups and only delete the older log backup records.


    jchang

    Monday, May 25, 2020 4:24 PM
  • I'm afraid there's little we can do if the history is purged. If its absolutely important to know the backup history then you may have to go through the different channels to get the older backup file of MSDB. (if its available)

    Maybe this wasn't asked before.. Can I understand what is the need to know the previous backup size? I guess this could be due to some capacity planning? Capacity planning is more than just about the space you need for databases. You have to understand the workload and what it requires in terms of CPU, memory, and disk resources. A base line needs to be set and continuous capturing of these parameters is required. Perhaps its also good, if knowing the backup size is critical in your environment you, could start capturing this in an specific table as a long term solution.


    Tuesday, May 26, 2020 9:47 AM
  • Chang,

    Thanks for your response. I completely agreed that needs keep information of Full backup history instead of purging.

    I will start to store the information into table so it will be easily available but mean while looking some options/resources to get the size as we are looking capacity planning and database growth.

    Wednesday, May 27, 2020 5:21 PM
  • Thanks JayaKrishna for your suggestion.

    we are looking to see the capacity planning and also more looking Database size growth as we have lots of servers and databases so it will be very helpful.

    currently we have purging process so don't have required information but looking if you or someone have other options.

    Thanks for the help!

    Wednesday, May 27, 2020 5:24 PM
  • Take note that approach of determining database growth based on backup will not be useful if the backupset table from the msdb is being purged or if there is not enough data on it to make a conclusive decision. Perhaps it would be good to start capturing this information from now on as in the long run this will help you.

    Alternatively you can try to check this post and see if this will be helpful in your situation. This may give you an idea how much the DB has grown recently based on the auto-grow and shrink events. 

    https://www.sqlshack.com/get-details-of-sql-server-database-growth-and-shrink-events/

    Wednesday, May 27, 2020 5:43 PM
  • Thanks JayaKrishna again for your valuable tips.

    I will check that url and try to look into.

    If i have to start to collect the data for Database growth or Capacity planning what will be the best method you suggest?

    Thanks for your help!

    Wednesday, May 27, 2020 10:56 PM
  • If i have to start to collect the data for Database growth or Capacity planning what will be the best method you suggest?

    What do you want to collect? The actual sizes of the files, or the amount of space actually allocated?

    Whichever, I would create a table keyed by database and date, and a job that runs daily that collects the data you need to that tabel.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 28, 2020 9:17 PM