Investigating SQL Server VDB Growth Due to Database Maintenance Jobs (KBA10769)

Issue

In the hours or days after a VDB is Provisioned or Refreshed, the database may show an unexpected growth in the size of space consumed on the Continuous Data Engine even though the database is not actively being used.

This might be visible from the Datasets screen (as Dataset Storage) and from the Storage Capacity Screen (as Current Copy Size).

In some cases, this issue may be caused by database maintenance jobs, such as index rebuilds or reorganization, that are being scheduled to run against all databases on your Target Environment, including Virtual Databases (VDBs).

Note:

This is part of a series of knowledge base articles that describe causes of unexpected database growth. For an overview of other possible causes, please see Investigating SQL Server VDB Growth (KBA10774).

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Date	Release
Jan 25, 2024	19.0.0.0
Dec 20, 2023 | Jan 10, 2024	18.0.0.0 | 18.0.0.1
Nov 21, 2023	17.0.0.0
Oct 18, 2023	16.0.0.0
Sep 21, 2023	15.0.0.0
Aug 24, 2023	14.0.0.0
Jul 24, 2023	13.0.0.0
Jun 21, 2023	12.0.0.0
May 25, 2023	11.0.0.0
Apr 13, 2023	10.0.0.0 | 10.0.0.1
Mar 13, 2023 | Mar 20, 2023	9.0.0.0 | 9.0.0.1
Feb 13, 2023	8.0.0.0
Jan 12, 2023	7.0.0.0
Releases Prior to 2023
Major Release	All Sub Releases
6.0	6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0, 6.0.12.0, 6.0.12.1, 6.0.13.0, 6.0.13.1, 6.0.14.0, 6.0.15.0, 6.0.16.0, 6.0.17.0, 6.0.17.1, 6.0.17.2
5.3	5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0
5.2	5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1
5.1	5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0
5.0	5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

Troubleshooting

To determine if unexpected growth is triggered by a database maintenance job, it is most helpful to determine when the activity is being triggered.

From the Resources → Dataset Performance screen, use the Day timescale, and deselect VDBs other than the one which has grown unexpectedly.

Note:

This view only provides a history for the last 24 hours. If the issue occurred less recently than this, it may be helpful to Provision a new VDB specifically for the purposes of testing, and then monitor it for changes.

 

In this example, a sudden spike in database throughput is visible starting at 3am local time.

 

Once this information has been gathered, connect to the Target Database Instance via SQL Server Management Studio, and then check the SQL Server Agent for any evidence of database maintenance jobs running at this time.

The SQL Server Job Activity Monitor can summarize when some jobs were most recently executed. In this example, we can see that a job had been executed at 3am server time (note that the server time may vary from the browser time in the Dataset Performance screen):

 

Note:

In some organizations, jobs may be run or orchestrated by external systems.

 

The following type of database maintenance job are most likely to generate high levels of database write activity:

• Index Rebuild
• Index Reorganize (Defragmentation)
• Shrink Database
• Update Statistics, for very large databases
• Third party scripts, such as MaintenanceSolution.sql, which co-ordinate these tasks

The above jobs typically modify or relocate a large number of database blocks to different locations on the disk that hosts the database.

The Continuous Data Engine tracks the movement of each block on disk in the same way that it would track changes to table data, and the space used by the VDB (represented by the Current Copy Size) will increase.

Check Database tasks may also generate high levels of database activity, but will not typically write to the database.

Resolution

If VDB growth is being triggered by database maintenance jobs, consider whether VDBs can be excluded from your database maintenance tasks.

If these jobs cannot be disabled, or are run intentionally, growth in the VDB Current Copy Size is normal and expected.

Virtual Databases (VDBs) will inherit the physical characteristics of the dSource or VDB from which they were provisioned. This includes fragmentation and statistics of the database at the time of the database snapshot.

If there are already processes in place that perform this maintenance on your Source Database, it may not be necessary to run these tasks on your VDBs.

 

---