Microsoft KB Archive/890544

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

Article ID: 890544

Article Last Modified on 2/7/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-





SYMPTOMS
When you run a snapshot backup command from a Microsoft SQL Server Virtual Device Interface (VDI) application, the backup may not be finished successfully if the following conditions are true:
 * Multiple VDI snapshot backups are occurring.
 * The thaw command (CompleteCommand) has not been issued for all databases.

Additionally, you may notice a waittype value of 0x0081 in the sysprocesses table for the server process ID (SPID) that issued the backup command. The 0x0081 waittype value indicates that the SPID is waiting for the transaction log to be flushed to disk. This behavior can occur even when the VDI application has issued the thaw command (CompleteCommand) for that particular snapshot backup.



CAUSE
This behavior occurs when the VDI application issues multiple snapshot backup commands and the VDI application has not issued the thaw commands (CompleteCommand) to all databases.



WORKAROUND
To work around this behavior, the VDI application must issue the thaw commands (CompleteCommand) to all databases as soon as possible. We do not recommend that you wait for one database backup to finish before sending the thaw command (CompleteCommand) to a second database backup. This behavior may cause the first backup to be unsuccessful and stop responding (hang). It may also cause SQL Server to stop responding. If the second database backup is finished, you must issue the thaw command (CompleteCommand) immediately.



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.
 * 1) SQL Server issues the VDC_Shapshot command to the VDI application.
 * 2) SQL Server waits for the VDI application to finish backing up the database and log files.
 * 3) 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.
 * 1) 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.
 * 2) 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:   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   Create a user table by using the following Transact-SQL script: create table test2.dbo.t(c1 int) go  From your VDI application, issue a snapshot backup command to the test1 database, and then wait. From your VDI application, issue a snapshot backup command to the test2 database, and then wait.  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.  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.</li>  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. </li> 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.</li></ol>

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.

Additional query words: SQL2000

Keywords: kbinfo kbtshoot kbprb kbbackup KB890544

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.