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

Source: Internet
Author: User

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:

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.