Welcome to SqlServer-QA.net Sign in | Join | Help

SQL Server Storage Engine & Tools (SSQA.net)

SQL Server Tools includes storage engine that includes a complete set of graphical tools and command prompt utilities that allow users, programmers, and administrators. SSMS, SSRS, SSIS & SSAS are most commonly used tools.
SQL 2005 snapshot backups and disk space monitoring

When using SNAPSHOT backups in SQL Server there are few things you need to consider, that included monitoring the disk space usage where the database backups & snapshot files are stored.

BOL confirms about SNAPSHOT backups as:

The underlying backup technology creates an instantaneous copy of the data that is being backed up. The instantaneous copying is typically accomplished by splitting a mirrored set of disks or by creating a copy of a disk block when it is written. This preserves the original. At restore time, the original is made available immediately and synchronization of the underlying disks occurs in the background. This results in almost instantaneous restore operations.

Things to consider:

As you are aware database Snapshots operate at a data-page level and the original page is copied from the source database using the copy-on-write operation. Obviously the updates to the database subsequently will not have any affect or changes to the snapshot.

As these pages (snapshot) are stored in sparse files (an NTFS feature) which are empty files that contain no user data and do not have disk space for user data allocated to it as yet. However, sparse files can grow in size and occupy disk space as the snapshots get stored into it.

So on the basis of the growth rate of the snapshot and the disk space available in the sparse file. If the disk drive fills up the write operations to all snapshots will fail. Hence you should study the typical update patterns for the database and plan the amount of space required and also plan for the lifespan of the snapshot.

Posted: Wednesday, June 13, 2007 3:20 AM by SQL Master

Comments

SSQA - SqlServer-QA.net said:

When using SNAPSHOT backups in SQL Server there are few things you need to consider, that included monitoring

# June 13, 2007 4:44 AM
Anonymous comments are disabled