SQL Server Q&A

Symptoms
When you run a RESTORE DATABASE WITH RECOVERY statement to bring a standby server online, you might receive one of the following error messages:

Error: 9003
The LSN %S_LSN passed to log scan in database ‘%.*ls’ is invalid.

Error: 9004
An error occurred while processing the log for database ‘%.*ls’.
Resolution
This problem occurs because physical characteristics (virtual log parity) for inactive virtual log files in the transaction log are not preserved when a full database backup is restored.

Symptoms
For a suspect database, you can back up the current transaction log by using the Transact-SQL NO_TRUNCATE option.
When you try to do so, if the database is suspect because the secondary data file is not available, the query may fail with the following messages in SQL Query Analyzer:

Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 8 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.The following stack dump is written to the SQL Server 7.0 RTM error log file:

———————————————————————-Short Stack Dump0×41091082 Module(ums+1082) (UmsListBase::InsertTail+19)0×410913aa Module(ums+13aa) (UmsList<UmsTimerRequest,0>::InsertTail+1c)0×41091379 Module(ums+1379) (UmsQueue<UmsUserContext,0>::EnqueueTail+13)0×410915e8 Module(ums+15e8) (UmsEvent::Wait+4c)0×0057c28b Module(sqlservr+17c28b) (FCBIoRequestDispenser::GetIoreq+4c)0×00409bd3 Module(sqlservr+9bd3) (FCB::AsyncRead+13)0×00409eea Module(sqlservr+9eea) (DBTABLE::ScatterRead+65)0×00409e73 Module(sqlservr+9e73) (bufgetfromdisk+1c3)0×00402954 Module(sqlservr+2954) (bufget+13c)0×00405229 Module(sqlservr+5229) (getpage2+68)0×00404e8f Module(sqlservr+4e8f) (srchindex+131)0×00407392 Module(sqlservr+7392) (prepscan+ce)0×00407230 Module(sqlservr+7230) (LinkedPageSupplier::FirstPage+1d)0×0040700c Module(sqlservr+700c) (LinkedPageSupplier::GetNextPage+41f)0×00406e90 Module(sqlservr+6e90) (SDES::GetBiDi+1c2)0×004102b7 Module(sqlservr+102b7) (createdes+28c)0×00402cdb Module(sqlservr+2cdb) (opentabid+1b2)0×00406052 Module(sqlservr+6052) (CSysScan::OpenScan+b4)0×00405da8 Module(sqlservr+5da8) (CSysScan::StartScan+93)0×004c43eb Module(sqlservr+c43eb) (CFileGroupScan::FStartScanAll+20)0×0071b9a6 Module(sqlservr+31b9a6) (BackupOperation::LoadFileGroupList+36)0×006f64cd Module(sqlservr+2f64cd) (BackupOperation::SnapshotMetadata+1d8)0×006fe313 Module(sqlservr+2fe313) (BackupEntry::BackupLog+3ec)0×005e0821 Module(sqlservr+1e0821) (CStmtDumpXact::XretExecute+5b)0×00419efa Module(sqlservr+19efa) (CMsqlExecContext::ExecuteStmts+118)0×00419a0f Module(sqlservr+19a0f) (CMsqlExecContext::Execute+16b)————————————————————————-
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
Consider the following scenario. The SQL Writer service is running on a computer that has Microsoft SQL Server 2005 installed. You use SQL Server Management Studio to back up a database. Additionally, you use the Backup tool (NTBackup.exe) to back up the database files. Then, you try to restore the database from the backup set by using SQL Server Management Studio. In this scenario, you receive an error message that resembles the following:

Restore failed for Server ‘<ServerName>’.(Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device ‘<GUID of the device>’. Operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)If you examine the start date and the finish date of the backup set, you find that the dates are updated to the date that you used the Backup tool.
Resolution
This problem occurs because the Backup tool uses the SQL Writer service to back up SQL Server 2005 databases. The Backup tool triggers the SQL Writer service to create a snapshot. After the SQL Writer service creates the snapshot, the Backup tool determines whether the database files are on the exclude list. The database files include .ldf files and .mdf files. If the database files are on the exclude list, the Backup tool deletes the database files from the snapshot. However, the Backup tool adds entries in the backupset table in the msdb database.

Symptoms
Consider the following scenario. In Microsoft SQL Server 2005, you restore a database from a backup. Then, you try to open a full-text catalog by using SQL Server Management Studio. In this scenario, you may receive the following error message:

Property IsAccentSensitive is not available for FullTextCatalog ‘[CatalogName]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo) Additionally, when you try to query the information about the AccentSensitivity property of the full-text catalog by using the FULLTEXTCATALOGPROPERTY Transact-SQL function, you receive a null value. For example, you use a Transact-SQL statement that resembels the following statement to query the information about the AccentSensitivity property.

select FULLTEXTCATALOGPROPERTY(‘<CatalogName>’,'AccentSensitivity’)When you use this statement, you receive a null value. However, when you query the information about the AccentSensitivity property directly from the full-text catalog view, SQL Server returns the correct information. For example, you can use the following Transact-SQL statement to query the information.

select * from sys.fulltext_catalogsThis problem may occur when the following conditions are true: You back up a database and then delete the database.The database has a full-text catalog.Before you restore the database, you create one or more databases. Therefore, the previous database ID is used by the newly created databases.
Resolution
This problem occurs because SQL Server stores a wrong full-text catalog name for the existing full-text catalog when you restore the database.

Symptoms
When you perform a full backup of a database in Microsoft SQL Server 2005, you may receive an error message that resembles the following:

Backup failed for Server ‘ComputerName\SQLInstanceName’.(Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The backup of the file or filegroup “sysft_ FullTextCatalog” is not permitted because it is not online. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)If you perform a full backup of a database in SQL Server 2005 Service Pack 2 (SP2), you may receive an error message that resembles the following:

The backup of full-text catalog ‘FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog becomes offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Resolution
This behavior occurs because a full-text catalog in the database is not online. To perform a full backup, SQL Server 2005 requires all the database files andfull-text catalogs in the database to be online.
The full-text catalog may be online because one or more of the following conditions are true:The full-text catalog folder is either deleted or corrupted.You did not enable the database for full-text indexing.The database is restored from a Microsoft SQL Server 2000 database backup. Therefore, the folder of the full-text catalog in the database does not exist on the server where you restore the database.The instance of SQL Server 2005 that you are running was upgraded from SQL Server 2000. However, the full-text search service cannot be accessed during the upgrade.The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.Note In SQL Server 2005 SP2, the reason that the full-text catalog is not online is logged in the SQL Server error log. See the SQL Server error log for the specific cause of this behavior.

Symptoms
When you perform a database backup to disk or tape, or a restore from disk or tape, the following error message may occur:
SQL Server 7.0

Server: Msg 3266, Level 16, State 1, Line 1
The Microsoft Tape Format (MTF) soft filemark database on backup device ‘devicename’ cannot be read, inhibiting random access.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally. SQL Server 2000

Server: Msg 3266, Level 16, State 1, Line 1
The backup data in ‘devicename’ is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally. SQL Server 2005

Server: Msg 3013, Level 16, State 1, Line 1
The backup data at the end of ‘devicename’ is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Resolution
A filemark in the backup device could not be read. There are many reasons why you may encounter a filemark error. Some of the reasons include the following :A media failure may occur on the device where the backup is located.A write failure may occur during the creation of the backup.
For example, a loss of connectivity may occur during a network backup. Or, a failure of the IO path to flush the write to disk may occur after the write to disk was reported to SQL server as successful.