SQL Server Snapshot Sparse File Size

When you execute the CREATE AS SNAPSHOT sql statement, the snapshot creates a NTFS sparse file. In a sparse file only “nonzero” information is stored on the disk. The maximum size of a database snapshot is the size of the source database at snapshot creation. In Windows Explorer, the snapshot files are shown with a size matching that of the source database. However, when you go into the file properties you will see that the size on disk is 0KB or a multiple of 64KB (representing 1-8 data pages) if some data has been transferred.

At the beginning, there is no source/user data in the snapshot file. It’s only after changes are made in the source database that data pages get written to the snapshot file. When a data page in the source database is changed, the original data page is first copied to the snapshot file before updating in the source database (copy-on-write). The first time any page is written to the sparse file the entire 64K extent is allocated. All other pages in the extent are written as zero's.

When a sparse file is populated, Windows limits the amount of data that may reside in the file. Once the amount of data stored in the sparse file exceeds the limit further data storage in the file may be prevented.

• Windows 2003 - 64GB (Error 1450 returned)
• Windows 2008 and Vista - 16GB (Error 665 returned)

Windows 2003 Error: The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

Windows 2008 Error: The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000005bd3dc000 in file 'Test.mdf:MSSQL_DBCC8'

Let’s take a look at what takes place when the snapshots are created.

• A sparse file is created. This file will have the same file size as the database. Remember, the only data that may be contained in the snapshot file at this point are pointers to the source database data files. Only after something changes in the source database will copy-on-write insert data into the snapshot file.
  
• The database is checkpointed (all dirty pages in the buffer cache are written to disk).
  
• If uncommitted transactions are in progress in the database, the snapshot will reflect the state of the database before these transactions started in other words, in the context of the snapshot, these transactions will be rolled back – but transactions in the database itself are not.
  
• The snapshot is brought online and is ready for use.
  

Quick Note:

From a Disaster Recovery standpoint, SQL Server snapshots are not very useful as they require an online database to function. In situations where there is a hardware failure, the database is no longer online and the snapshot becomes useless. Also, snapshots cannot be restored when users are accessing the database. So if there is a roll back in progress or some other process that does not allow the database’s state to be changed, snapshot restores can not be applied. However, in situations where an object was accidently deleted or altered, and needs to be restored, snapshots are ideal.

For a more detailed look inside how Copy-On-Write works, see: http://blogs.msdn.com/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx

Can’t Delete Jobs (Microsoft SQL Server, Error: 547)

I was creating a new job to handle weekly database maintenance and when I attempted to delete the old job, I received the following error;

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'Weekly_Maintenance'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

The statement has been terminated. (Microsoft SQL Server, Error: 547)
------------------------------

If you try deleting the job from the sysmaintplan_subplans table, you also get the following error:

Msg 547, Level 16, State 0, Line 41

The DELETE statement conflicted with the REFERENCE constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_log", column 'subplan_id'.

I wanted to create a stored procedure that I could just pass a job name to and it would check each of the tables and delete all references for me. Here is what I came up with.

CREATE PROC dbo.DropJob
@JobName AS VARCHAR(200) = NULL
AS
DECLARE @msg AS VARCHAR(500);

IF @JobName IS NULL
BEGIN
SET @msg = N'A job name must be supplied for parameter @JobName.';
RAISERROR(@msg,16,1);
RETURN;
END
IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName));

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
GO

Now you can call the SP with the following;

USE [msdb];
EXEC dbo.DropJob @JobName = N'Shrink_AWP_Databases.Subplan_1';