Skip to main content
Delphix

Investigating SQL Server VDB Growth (KBA10774)

  1. Last updated
  2. Save as PDF
 

 

KBA

KBA#
10774

 

Issue

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.

Growth may be visible from the Datasets screen, as Dataset Storage, or from the Storage Capacity Screen, as Current Copy Size.

Growth of VDBs over time can also be reviewed using the Historic Capacity feature, described in the document Reviewing historical capacity from the CLI.

In many cases, database growth is the result of genuine database write activity, such as database UPDATE and INSERT statements.

It is normal for VDBs to grow over time: the Continuous Data Engine must record each database change made to disk, and the space used to record those changes is reflected in the VDB Current Copy Size.

The engine also maintains historic versions (Snapshots and Timeflows) for each VDB, in accordance with the configured Retention Policy, as described in Understanding Delphix disk usage.

This article provides links to Knowledge Base articles that may be helpful in investigating situations where the database growth is abnormal and cannot be explained by normal database activity.

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 better understand the nature of the VDB growth:

  • Provision or Refresh a VDB.
  • Record the Start and Finish time of the Provision or Refresh job.
  • Use the Command Line interface to monitor the growth of the VDB over time (see document Reviewing historical capacity from the CLI).

 

tip

Tip:

It may be helpful to provision a new temporary VDB specifically for testing purposes, to ensure that growth is not being caused by users or applications. Note that it may take hours or days for unexpected database growth to be apparent.

The following matrix describes several possible causes of VDB growth, depending on when and how the growth occurs. Each is further described below, and, in some cases, separate KB articles describe the behavior in more detail.

Timing of VDB Growth VDB Growth Pattern Possible Causes
During VDB Provision Finishes at end of VDB Provision
  • Database Recovery
  • Hook Operation activity
  • Database masking
After VDB Provision Continues after VDB provision, then stops
  • Ghost Cleanup
After VDB Provision Ongoing or intermittent, depending on database workload
  • Routine VDB activity
  • Statistics Updates
  • Logs cannot be purged
  • Query Store maintenance
After VDB Provision Starts at fixed time, then stops
  • Database maintenance jobs
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

Routine database growth: recovery, hooks, masking and database changes

It is normal for VDBs to consume space while a VDB Provision or Refresh operation is running. This may be for a number of reasons:

  • Database recovery may be required, due to incomplete transactions at the time of the parent Snapshot.
  • Hook Operations may be run by the VDB operation, which modify large amounts of data.
  • A Database Masking Job may be defined for the VDB, which is removing or modifying sensitive data.

As described earlier, it is also normal for a database's size to grow over time in response to data changes by applications and database users.

Ghost Cleanup: KBA1033

SQL Server may initiate a "Ghost Cleanup" process shortly after database provisioning, which will remove deleted records that had not yet been purged from the parent container.

This is described more in the KB article Understanding VDB Size Under Microsoft SQL Server Ghost Cleanup Process (KBA1033).

Statistics updates: KBA10772

If the AUTO_UPDATE_STATISTICS database option was disabled on the parent container, queries against newly Provisioned or Refreshed VDBs may trigger automatic updates of database statistics.

This is described more in the KB article Investigating SQL Server VDB Growth due to Statistics Updates (KBA10772).

Logs cannot be purged

Some Source database configurations may cause SQL Server to unexpectedly hold onto transaction log entries in provisioned VDBs. This may include:

  • SQL Server Replication
  • Change Data Capture
  • Provisioned VDB uses the FULL recovery model and is not backed up

These situations will not result in spontaneous growth of the database, but may cause higher database growth than usual, preventing the database from re-using space in the transaction log.

The log_reuse_wait column in the SQL Server sys.databases view may be helpful in understanding whether this is a concern.

Query Store maintenance

The SQL Server Query Store is an optional feature available for databases in SQL Server 2016 and later, described in Microsoft's document Monitor performance by using the Query Store.

To maintain the Query Store, the SQL Server instance maintains metadata about queries being run on the database. This writes data regularly to the VDB, even in cases where there is no user workload, or where the workload is read-only.

Database maintenance jobs: KBA10769

Database maintenance jobs that move data within data files, such as Index Rebuilds, Index Reorganization (Defrag), or Shrink File activities, may perform a large number of writes if run against SQL Server VDBs.

This is described more in the KB article Investigating SQL Server VDB Growth due to Database maintenance jobs (KBA10769).

 

Privacy Policy Terms of Use Legal About
© 2024 Delphix Corp