SQL Server Q&A

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
A SELECT statement may generate an exception that causes the SQL Server instance to terminate unexpectedly if all of the following conditions are true: The SELECT statement retrieves a computed column.The computed column references a user defined function (UDF).The UDF accesses table data.An implicit data type conversion is necessary for one of the UDF parameters. No stack dump or other message related to the exception is written to the SQL Server error log.
Resolution
To 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 the Microsoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How To Obtain the Latest SQL Server 2000 Service PackNOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.
The English version of this fix should have the following file attributes or later:
SQL Server 2000 Service Pack 1
This hotfix build is designed to be applied on a server that is running SQL Server 2000 Service Pack 1:

VersionFile nameDescription————————————————8.00.0469Sqlservr.exeSP1-based hotfix SQL Server 2000 Service Pack 2
This hotfix build is designed to be applied on a server running SQL Server 2000 Service Pack 2:

VersionFile nameDescription————————————————8.00.0552Sqlservr.exeSP2-based hotfix NOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

Symptoms
Executing a SELECT * FROM SYSINDEXES query against a database that has been upgraded from Microsoft SQL Server 7.0 may cause an Access Violation. If an Access Violation occurs, an error message similar to the following displays in the query window:

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Server: Msg 1203, Level 20, State 1, Line 1
Process ID 51 attempting to unlock unowned resource KEY: 7:2:1 (790023da5d09).
Resolution
The maximum length defined for the keys column in the sysindexes system table in SQL Server 2000 is 1088, while in SQL Server 7.0, it is only 816. When a database is upgraded from SQL Server 7.0, the maximum length defined for the keys column is not updated. As a result, when an index whose keys column exceeds 816 bytes is created and the index is subsequently fetched, as in a SELECT * FROM SYSINDEXES query, the prefetch buffer is written past the 816 bytes that have been allocated, which causes the Access Violation.

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 you try to restore a database backup, the restoration may fail, and a retail assertion with error 3624 may occur. You may see the following error message 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.Whileyou take the database backup, transactions that involve multiple databases are logged in the transaction logs.You restore the database backup in SQL Server 2000 Service Pack 2 (SP2).
Resolution
A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.
If the hotfix is available for download, there is a “Hotfix download available” section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.
Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or tocreate a separate service request, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support(http://support.microsoft.com/contactus/?ws=support)Note The “Hotfix download available” form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.
The 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 namePlatform————————————————————————-09-Dec-200221:392000.80.714.07,471,185Sqlservr.exeX86 Note 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 2: