SQL Server Q&A

Symptoms
When you perform a backup or restore operation and the transaction log is greater than four gigabytes (or 2^32 bytes), the LogMgr::ValidateBackedupBlock method may cause the following error when it computes the offset of the log block in the physical file:

Error 3241 Severity 16 State 1
The media family on device ‘%ls’ is incorrectly formed. SQL Server cannot process this media family.
Resolution
LogMgr::ValidateBackedupBlock computes the log sequence number (LSN) block ID in 4-byte arithmetic (rather than 8-byte) which causes a DWORD overflow.

Symptoms
If the following conditions are true, SQL Server may encounter an assertion followed by an Access Violation (AV), which may then cause the server to either shut down or become unresponsive:
An ALTER DATABASE Set Read_Only statement has been issued on a database.The transaction log of the database on which the ALTER DATABASE statement is being run is full.
Resolution
When an ALTER DATABASE SET READ_ONLY statement is executed on a database, SQL Server must: Shut down the database cleanly by issuing a CHECKPOINT statement.Update the status in the Master..Sysdatabases system table for that database.Bring the database back up with the new status. However, if the transaction log is full, a CHECKPOINT record cannot be written to the database and it raises an error. SQL Server ignores this error and incorrectly continues to update the status in Master..Sysdatabases. As a result, when the database is brought back up again, the status is set to read-only but SQL Server continues to try to perform a recovery on a read-only database; therefore, an Assertion occurs.

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
According to the “Creating and Applying Transaction Log Backups” topic in SQL Server 7.0 Books Online, after a database file is added or removed from the database, you should perform a full database backup instead of a transaction log backup.
Because the information about the change in the database file structure is not included in the transaction log, this process breaks an existing sequence of transaction log backups. You need to perform a new full database backup to start a new sequence of transaction log backups.
However, even though this is recommended in SQL Server Books Online, SQL Server does not enforce this behavior but permits you to perform an apparently valid transaction log backup at that time without giving a warning that the backup sequence is broken.
If such an invalid backup is made and then an attempt to restore it is made later, the attempt to restore this transaction log fails with the following message:

Server: Msg 3155, Level 16, State 1, Line 1
The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
To work around this problem, perform a full database backup after modifying the file structure of a database.

Symptoms
If the log data file (LDF) for a database is not available during SQL Server startup (for example, if the file has been renamed or deleted), SQL Server 7.0 sometimes attempts to rebuild the LDF file automatically to ensure database availability.
Because the information from the original LDF file is lost, this process interrupts an existing sequence of transaction log backups. A new full database backup needs to be performed to start a new sequence on transaction log backups.
However, SQL Server permits you to perform an apparently valid transaction log backup at this time without warning you that the backup sequence is broken.
If you do make such an invalid backup and then later attempt to restore this transaction log, it fails with the following message:

Server: Msg 3155, Level 16, State 1, Line 1
The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
To work around this problem, do either of the following:Make sure that the LDF files are not deleted or renamed.
-or-Perform a full database backup to start the transaction log backup sequence over.