Aggregating historical backup information in SQL Server methods for comparing database growth _mssql

Many times, when we plan for the space of the SQL Server database, or to the storage space, we need to estimate the size of the required database space, the simplest way to estimate the future is to look at the past trends, which is often the most reasonable way.

Generally speaking, a good operational database requires a regular baseline (baseline) and a baseline to know what is normal. A simple example for example, some people's blood pressure is usually low, so 80 of the low pressure is not normal for him. 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 past accurate data.

One solution is to look at past data growth trends by looking at the size of historical backups, when a database backup does not back up the entire file, and only backs up pages that have been allocated in the FPS page, you can see that the database is 20G, but the backup is only 3G, so database backups can be used as a basis for viewing data growth.

The database records the backup information in the Msdb.dbo.backupset table each time it is backed up, so you can aggregate historical backup information through the following view to compare the growth of the database:

SELECT
[database_name] as "database",
DATEPART (Month,[backup_start_date]) as "month",
AVG ([backup_size ]/1024/1024) as "Backup Size MB" from
msdb.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

The result is shown in Figure 1.

Figure 1. Growth in historical data

where database_name in Listing 1 is replaced by the name of the database you want to see.

Note: policy-based management is introduced after SQL Server 2008, which establishes a job by default, as shown in Figure 2, which runs at 2 points per day and cleans up out-of-date history according to the policy (the history retains the number of days, as shown in Figure 3).

Figure 2. View Policy Management

Figure 3. The history retains the number of days, defaults to 0, never cleans up

This policy corresponds to establishing a job (as shown in Figure 4).

Figure 4. Clean up a history job

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