SQL Server Q&A

Symptoms
Consider the following scenario:You have a database mirroring session in Microsoft SQL Server 2005. The database mirroring session does not have a witness. You set up a witness server. On the principal server and on the witness server, you do not use DNS together with DHCP for name resolution. Instead, you specify that a host file should be used for name resolution. On the principal server instance, you try to set the witness.In this scenario, you receive the following error message:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://ServerName:port’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
Resolution
A database mirroring session must use a fully qualified domain name (FQDN) to resolve the name of the witness server.

Symptoms
The following error message may appear in the SQL Server Log Shipping Monitor View Restore History window:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3154: [Microsoft][ODBC SQL Server Driver][SQL Server]The backup set holds a backup of a database other than the existing ‘pubs_test’ database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormallyThe preceding error message occurs if: You configure multiple databases for log shipping.You back up the transaction logs for the databases to the same folder.The database names only differ by “_tlog” in the name. For example, pubs_test and pubs_test_tlog.
Resolution
The RESTORE job that runs on the secondary server incorrectly parses the transaction log backup file names and attempts to restore transaction logs that belong to a different database. This behavior does not cause any other problems on the primary or the secondary servers.

Symptoms
You may experience the following symptoms in SQL Server:A database does not expand automatically although you have the autogrow option on.The Errorlog file does not contain any messages that indicate that the database is full.Client computers that try to modify data in this database receive a “Timeout Expired” message on their user transaction.SQL Profiler displays an “Attention” event on a connection, and, if the computer is running SQL Server 7.0, SQL Profiler also displays an “Exception 822″ error.NOTE: The “Timeout Expired” message is a generic message that you may receive in other situations.
You may notice these symptoms when:You run SQL Profiler for a long time, you capture the Exception event and the IntegerData column, and then you look for an occurrence of the “Exception 822″ error.
-Or-You use the -y822 parameter to start SQL Server. After the database does not automatically expand, you check the Errorlog file for a stack dump because of the “Exception 822″ error.NOTE: The database file may automatically expand, but the timeout occurs when the file is being initialized for use, which must occur for the database to be useful.
Resolution
When a connection issues a Data Modification Lanaguage (DML) statement that requires additional space in a database, SQL Server tries to automatically expand the database, if you have the autogrow option set. The client that issued the statement may time out while the database is expanding, depending on the query timeout value that is configured on the client side. Several applications use a default timeout value of 30 seconds. This timeout ends the client connection and the automatic expansion of the database that it prompted.

Symptoms
If you back up a database with SQL Enterprise Manager (SEM) and you check Verify backup upon completion under the Option tab, SQL Server issues the following commands: BACKUP DATABASE and RESTORE VERIFYONLY. RESTORE VERIFYONLY checks whether the backup set is complete and whether all volumes are readable.
For example, if you back up the Pubs database to a backup device called Test, SQL server runs the following two SQL commands:

BACKUP DATABASE [pubs] TO [test] WITHINIT ,NOUNLOAD ,NAME = N’pubs backup’,NOSKIP ,STATS = 10,NOFORMAT goRESTORE VERIFYONLY FROM [test] WITHFILE = 1 ,NOUNLOAD However, if you schedule a job through the SQL Enterprise Manager to do the same database back up, SQL Server only schedules BACKUP DATABASE without the RESTORE VERIFYONLY option.
Resolution
SQL Server 2000To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack
SQL Server 7.0To resolve this problem, obtain the latest service pack for SQL Server 7.0. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
301511?(http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

Symptoms
A restore of the msdb system database leaves the database in a loading state (unrecovered) and changes in the differential and log backups are not applied if the following conditions are true:The restore is performed from Enterprise Manager (SEM).The restore operation is a combination of:
A full database backup and a differential backup.A full database backup and transaction log backups.A full database backup and a combination of differential and transaction log backups.NOTE: To perform transaction log backups of the msdb database in SQL Server 2000, you must change the recovery model to “bulk logged” or “full.” In SQL Server 7.0, you must have the “trunc. log on chkpt.” setting turned off for the msdb database.
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
301511?(http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

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