A snapshot backup may not be finished successfully when multiple VDI snapshot backups occur in SQL Server 2000 (890544)

MORE INFORMATION

When you must perform multiple VDI snapshot database backups, the snapshot backup process should be similar to the following:

1. The VDI application issues the snapshot backup command.
   
   Note The database IO is now frozen in SQL Server.
2. SQL Server issues the VDC_Shapshot command to the VDI application.
3. SQL Server waits for the VDI application to finish backing up the database and log files.
4. When the VDI application finishes backing up the database and log files, it sends a thaw command (CompleteCommand) to SQL Server.
   
   Note The database IO is now thawed in SQL Server.
5. SQL Server writes to the boot page of the database that a backup has occurred. Then, SQL Server flushes the boot page to disk. This step also requires a transaction log flush to disk.
6. The backup command is now considered finished.

Note If step 5 cannot finish because the log writer cannot flush the transaction log, the backup command cannot be completed. If the VDI application implements a synchronous call for the backup command, the call will not return. If the VDI application implements an asynchronous call for the backup command, subsequent calls will return the SQL_STILL_EXECUTING code value.

Steps to reproduce the behavior

To reproduce this behavior with only two snapshot backups, follow these steps:

1. In SQL Query Analyzer, run the following Transact-SQL script to create two databases on your server:

   use master
   go
   create database test1
   go
   create database test2
   go

2. Create a user table by using the following Transact-SQL script:

   create table test2.dbo.t(c1 int)
   go

3. From your VDI application, issue a snapshot backup command to the test1 database, and then wait.
4. From your VDI application, issue a snapshot backup command to the test2 database, and then wait.
5. In SQL Query Analyzer, run the following Transact-SQL script:

   begin transaction
   insert into test2.dbo.t values(1)
   commit transaction
   go 

   Note This command will be blocked because the test2 database is frozen in step 4.
6. Issue the thaw command (CompleteCommand) for the test1 database from your VDI application.
   
   Note You will notice that the backup database command for test1 cannot complete because it must wait for the transaction log to flush. The commit transaction statement in step 5 has blocked the log writer. Therefore, the backup on test1 cannot flush the transaction log and complete the backup.
7. In SQL Query Analyzer, run the following query to examine the sysprocesses table:

   select * from sysprocesses 

   Note Notice that the SPID that issued the backup for test1 has a waittype value of 0x0081. The SPID is waiting for the transaction log to flush.
8. From your VDI application, issue the thaw command (CompleteCommand) for the test2 database backup.
   
   Note Notice that the backup command for the test1 database is finished.

To successfully complete all databases backups with snapshot, you must issue the thaw command (CompleteCommand) to all databases that are frozen as soon as possible.