SQL Server Q&A

Symptoms
In Microsoft SQL Server 2000 Service Pack 3 (SP3), when you try to restore a database backup, the restoration may fail, and a retail assertion with error 3624 may occur. You may see an error message that is similar to the following in the SQL Server error log files as a result of the assertion error:

SQL Server Assertion: File: <xdes.cpp>, line=9860
Failed Assertion = ‘IS_OFF_SAFE (XDES_OUTCOME_ONLY, m_status)’
This problem occurs when all the following conditions are true: You take the database backup from a Microsoft SQL Server 7.0 database.While you take the database backup, transactions that involve multiple databases are logged in the transaction logs.You restore the database backup in Microsoft SQL Server 2000.
Resolution
Service pack informationTo resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/) How to obtain the latest SQL Server 2000 service pack
Hotfix informationThe English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

DateTimeVersionSizeFile name————————————————————————–07-Feb-200316:14786,432 bytesDistmdl.ldf07-Feb-200316:142,359,296 bytesDistmdl.mdf29-Jan-200312:25180 bytesDrop_repl_hotfix.sql31-Jan-200311:022000.80.763.01,557,052 bytesDtsui.dll29-Jan-200312:251,402 bytesInst_repl_hotfix.sql07-Feb-200317:102000.80.765.090,692 bytesMsgprox.dll29-Jan-200311:487.0.9466.0344,064 bytesMsvcr70.dll29-Jan-200311:482000.80.722.057,904 bytesOsql.exe29-Jan-200315:48746,470 bytesInstdist.sql07-Feb-200314:231,065,895 bytesReplmerg.sql07-Feb-200317:102000.80.765.0221,768 bytesReplprov.dll07-Feb-200317:102000.80.765.0307,784 bytesReplrec.dll29-Jan-200315:481,084,318 bytesReplsys.sql29-Jan-200311:482000.80.534.0127,548 bytesSqdedev.dll29-Jan-200311:482000.80.194.0180,290 bytesSqlunirl.dll07-Feb-200317:102000.80.765.0176,696 bytesSqlmap70.dll07-Feb-200317:102000.80.765.057,920 bytesSqlrepss.dll07-Feb-200317:112000.80.765.07,528,529 bytesSqlservr.exe07-Feb-200317:102000.80.765.045,644 bytesSqlvdi.dll07-Feb-200317:102000.80.765.082,492 bytesSsnetlib.dllNote Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.
Important This hotfix build is designed for a server that is running Microsoft SQL Server 2000 Service Pack 3.

Symptoms
When a database is backed up to multiple disk files, and one of the files in the backup set is deleted and then used again in the same backup set to back up a database, a restore of the database using this backup set may fail with the following message:

Server: Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 1 on device ‘d:\north2.bak’.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
Microsoft has confirmed this to be a problem in SQL Server7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799?(http://support.microsoft.com/kb/274799/) INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0For more information, contact your primary support provider.

Symptoms
When restoring the backup of a database, which is enabled for Transactional and Merge Replication (Sysdatabases.Category = 5), to a non-published database on a different server, the merge replication system tables are not dropped and the user tables on the destination server’s database cannot be dropped. If the backup is restored on the same server (different database), then the restore works fine and the replication information is removed.
Also, the restore works fine across servers when the source database is enabled either for Transactional or Merge Replication only (Sysdatabases.Category = 1 OR 4).
Resolution
To work around this problem, you can either:Enable the source database for Transactional or Merge Replication alone (Sysdatabases.Category = 1 OR 4).
-or-Drop the merge system tables using the DROP TABLE syntax.NOTE: Contact Microsoft Product Support Services if you need to drop the user tables and are unable to do so.

Symptoms
Consider one of the following scenarios:You restore a database backup from Microsoft SQL Server 2000 on an instance of Microsoft SQL Server 2005 by using the WITH RECOVERY option. You also restore this database backup on another instance of SQL Server 2005 by using the WITH NORECOVERY option.You have two instances of SQL Server 2000 that have a database that is the same on these instances. One instance has the database in the Loading status. The other instance has the database in the regular status. Then, you upgrade these instances to SQL Server 2005.You configure database mirroring on this database in SQL Server 2005. However, when you start database mirroring, you receive the following error message:

The Service Broker ID for the remote copy of database “<DatabaseName>” does not match the ID on the principal server. (Microsoft SQL Server, Error: 1436)Note This problem also occurs in Microsoft SQL Server 2008.
You usually encounter this problem when you upgrade a Microsoft SQL Server 2000 log shipping configuration. Consider the following scenario:You have a SQL Server 2000 log shipping configuration.You upgrade the servers that are involved in the log shipping configuration to SQL Server 2005.After the upgrade, you configure database mirroring by using the database that is used previously in the log shipping configuration.When you start database mirroring, you receive this error message.
Resolution
Databases in SQL Server 2000 do not have a Service Broker ID. If you restore these databases on instances of SQL Server 2005 by using the WITH NORECOVERY option, these databases will not be upgraded. Therefore, theService Broker ID of these databases is NULL after the restore. Additionally, if you upgrade an instance ofSQL Server 2000, existing databases that are in the Loading status will not be upgraded. Therefore, theService Broker ID of these databases is NULL after the upgrade. When you start database mirroring, the Service Broker ID of the principal server is compared to the Service ID of the mirror server. Because the Service Broker IDs do not match, the comparison fails.
In the upgrade scenario of a SQL Server 2000 log shipping configuration, the secondary database is in the Loading status. After you upgrade the server that holds the secondary database, the database does not have a Service Broker ID. After you upgrade the server that holds the primary database, the database has a Service Broker ID. When you start database mirroring, the Service Broker ID of the principal server is sent to the mirror server for comparison. Because the Service Broker ID of the mirror server is NULL, the comparison fails.

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by this hotfix packageThe prerequisites for installing the hotfix packageWhether you must restart the computer after you install the hotfix packageWhether the hotfix package is replaced by any other hotfix packageWhether you must make any registry changesThe files that are contained in the hotfix package
Resolution
When you restore a transaction-log backup that is generated from an instance of Microsoft SQL Server 2000 with Service Pack 4 (SP4) to an instance of Microsoft SQL Server 2005, you receive anerror message intermittently. The error message resembles the following:

Msg 3456, Level 16, State 1, Line 1
Could not redo log record (11841:73811:36), for transaction ID (0:13210866), on page (3:7), database ‘&lt;DatabaseName>’ (database ID 6). Page: LSN = (11827:5010:2), type = 17. Log: OpCode = 17, context 25, PrevPageLSN: (11841:73692:1017).
Restore from a backup of the database, or repair the database.

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by this hotfix packageThe prerequisites for installing the hotfix packageInformation about whether you must restart the computer after you install the hotfix packageInformation about whether the hotfix package is replaced by any other hotfix packageInformation about whether you must make any registry changesThe files that are contained in the hotfix package
Resolution
In SQL Server 2005, an error message that resembles the following is logged in the SQL Server error log after you roll back transactions on a table:

Error: 3315, Severity: 21, State: 1
During rollback, the following process did not hold an expected lock: process 51 with mode 8 at level 2 for row Rid pageid is (1:73) and row num is 0×0 in database ‘DatabaseName’ under transaction (0:546). Restore a backup of the database, or repair the database.This problem occurs when page locks are disabled on the table before the transactions are rolled back.
NotesIn the error message, DatabaseName represents the database that contains the table.You may have run the sp_indexoption stored procedure todisable the page locks on the table.For more information about how to view the SQL Server error log, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms187885.aspx(http://msdn2.microsoft.com/en-us/library/ms187885.aspx)