SQL Server Q&A

Symptoms
When you use SQL Server Enterprise Manager to backup and verify the backup of a database, the verification assessment of the backup is not reliable if the backup set is appended to either a previously existing file or device.
Furthermore, if you attempt to establish a Database Maintenance Plan through the Database Maintenance Wizard and you select Verify the Backup to a tape that contains a Microsoft Windows NT file backup that is File number 1, you will see this error message:

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 3143: [Microsoft][ODBC SQL Server Driver][SQL Server]
The data set on device ‘\\.\Tape0′ is not a SQL Server backup set.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore
operation terminating.Also, if you put a SQL Server database backup on the tape first and you then attempt to perform a Windows NT backup, a Dr. Watson error (0xc000005) occurs.
Resolution
Here are a few ways to work around this problem:Run this line of code from Query Analyzer to the backup device or file to identify the last backup set in the file:

RESTORE HEADERONLY Next, run this code:

RESTORE VERIFYONLY FROMDISK = N’c:\BACKUP\pubsbk.jnk’ WITHFILE = X ,NOUNLOAD where X is the maximum file id identified by the Position output column from RESTORE HEADERONLY. If you use the Database Maintenance Wizard, alter the previous RESTORE statement in the Schedule Task.
-or-
Consider selecting the Overwrite Existing Media option to make sure that the backed up file is always the first backup set in the file.
-or-
Consider backing up the SQL Server databases to a tape on which only SQL Server database backups exist.
-or-
Consider scripting the commands and then run the commands from a command prompt or batch file.For example:

DECLARE @DBNameVARCHAR(200),@TargetLocation VARCHAR(1000),@BackupNameVARCHAR(200),@FileNumberVARCHAR(10),@xpcmdStringVARCHAR(2000),@PrintStringVARCHAR(200)SELECT @DBName= ‘[pubs]‘,@TargetLocation = ‘c:\BACKUP\pubsbk.jnk’ ,@BackupName= ‘pubs backup’SELECT @xpcmdString = ‘BACKUP DATABASE ‘+@DBName+’ TO DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)+’ WITHNOINIT ,NOUNLOAD ,NAME = N’ + CHAR(39)+@BackupName+CHAR(39)+’,NOSKIP ,STATS = 10,NOFORMAT ‘EXEC (@xpcmdString)CREATE TABLE #TempBackupResults(BackupName nvarchar(128),BackupDescriptionnvarchar(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName nvarchar(128),ServerName nvarchar(128),DatabaseName nvarchar(128),DatabaseVersionint,DatabaseCreationDatedatetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSNnumeric(25,0),DatabaseBackupLSNnumeric(25,0),BackupStartDatedatetime,BackupFinishDatedatetime,SortOrder smallint, CodePage smallint,UnicodeLocaleId int,UnicodeComparisonStyle int,CompatibilityLeveltinyint,SoftwareVendorIdint,SoftwareVersionMajorint,SoftwareVersionMinorint,SoftwareVersionBuildint,MachineName nvarchar(128))SELECT @xpcmdString = ‘RESTORE HEADERONLY FROM DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)INSERT#TempBackupResults EXEC (@xpcmdString)SELECT @FileNumber = CONVERT(VARCHAR(20), MAX(Position))FROM #TempBackupResults WHERE BackupName=@BackupNameSELECT @xpcmdString = ‘RESTORE VERIFYONLY FROM DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)+’ WITHFILE = ‘ + @FileNumberSELECT @PrintString = ‘Verifying Integrity of Backup Set Number ‘ + @FileNumberPRINT @PrintStringEXEC (@xpcmdString)DROP TABLE #TempBackupResults

Symptoms
When you adjust the maximum worker threads configuration by using code similar to the following:

sp_configure ‘max worker threads’ The run value is updated immediately and you do not have to stop and restart the SQL Server service. However, the User Mode Scheduler picks up the newly configured max worker threads only after you stop and then restart the SQL Server service.
Resolution
Stop and restart SQL Server so that the new configuration settings can take effect.

Symptoms
Consider the following scenario. In Microsoft SQL Server, you create a snapshot backup of many databases at the same time. To do this, you use the Volume Shadow Copy Service (VSS), or you use the Virtual Backup Device Interface (VDI). In this scenario, the snapshot backup operation fails. Additionally, you receive the following error message if you use the VSS to create the snapshot backup:

ERROR: Selected writer ‘Microsoft Writer (Service State)’ is in failed state!
– Status: 8 (VSS_WS_FAILED_AT_PREPARE_SNAPSHOT)
- Writer Failure code: 0×800423f4 (<Unknown error code>)
- Writer ID: {WriterID}
- Instance ID: {InstanceID}You receive one of the following error messages if you use the VDI to create the snapshot backup:
Error message 1

[Microsoft][ODBC SQL Server Driver][SQL Server] Insufficient resources to create UMS scheduler.
Msg 3267, SevLevel 16, State 1, SQLState 42000
Error message 2

[Microsoft][ODBC SQL Server Driver][SQL Server] Could not create worker thread.
Msg 3013, SevLevel 16, State 1, SQLState 42000
Error message 3The following message may be logged in the SQL Server error log:

2008-12-06 02:26:24.90 spid420 SubprocessMgr::EnqueueSubprocess: Limit on ‘Max worker threads’ reachedThe number of databases that you try to back up when this problem occurs varies. The number of databases depends on the following conditions:The configuration of SQL ServerOther activities in SQL Server
Resolution
In SQL Server, the snapshot backup of each database uses five threads in the Sqlservr.exe process. Additionally, other activities may also use threads in the Sqlservr.exe process. Depending on the configuration of SQL Server, the available threads may be used up if you create a snapshot backup of many databases at the same time.