Find SQL Server database history growth information

Original: Find SQL Server database history growth information

Most of the time, when we plan the space for a SQL Server database, or space for storage, we need to estimate the size of the required database space, and the simplest way to estimate the future is to look at past trends, which is often the most logical way.

Generally speaking, a well-functioning database requires a regular baseline (baseline), and a baseline will not know what is normal. A simple example, for example, some people's blood pressure is usually low, then 80 of the low pressure for him is not normal. But the reality is that most systems do not have the habit of collecting baselines, so when planning space is needed to see historical growth, there is no accurate data for the past.

One solution is to look at the size of the historical backup of the past trend of data growth, the database backup will not back up the entire file, and only back up in the FPS page marked already allocated pages, can appear the database is 20G, but the backup only 3G, so database backup can be used as a basis for viewing data growth.

The database records information about the backup in the Msdb.dbo.backupset table each time it is backed up, so you can compare the growth of the database by aggregating historical backup information by looking at the following:

SELECT
[database_name] as"Database",
DATEPART (Month, [Backup_start_date]) as"Month",
AVG([backup_size]/1024/1024) as"Backup SizeMB "
 fromMsdb.dbo.backupset
WHERE
[database_name] = N' Adventureworks2012 '
 and[Type] =' D '
GROUP  by[Database_name],datepart (Mm,[backup_start_date]);

Code Listing 1. View the growth of historical data by month

As shown in result 1.

Figure 1: Growth of historical data

In the code Listing 1, database_name is replaced with the name of the database you need to view.

Note: After SQL Server 2008 has introduced policy-based management, the policy establishes a job by default, 2 shows that the job runs at 2 points per day and cleans up stale history by policy (as shown in history retention days, 3).

Figure 2: View Policy Management

Figure 3. History retention days, default is 0, never clean up

The policy corresponds to the creation of a job (shown in 4).

Figure 4: Cleanup History jobs

If the job or an artificially created job clears the data from the Msdb.dbo.backupset table, the results in Listing 1 may be affected.

Find SQL Server database history growth information