SQL Server Q&A

Symptoms
You may receive an access violation exception if all the following conditions are true:You connect to a linked SQL server by using remote access.You run the DBCC FREEPROCCACHE Transact-SQL statement on the linked server to remove all elements from the procedure cache.The remote access connection is interrupted when you remove all elements from the procedure cache.You may notice the following short stack dump file that corresponds to the access violation:

* Short Stack Dump* 004AE996 Module(sqlservr+000AE996) (CLob::DestroyDeep+00000026 Line 907+0000000F)* 008CB71E Module(sqlservr+004CB71E) (CXVariant::ClearDeep+00000116 Line 4519+00000000)* 004A4D82 Module(sqlservr+000A4D82) (CXVariant::Clear+00000022 Line 325+00000000)* 004C6F66 Module(sqlservr+000C6F66) (CAutoClearXVariant::~CAutoClearXVariant+00000016 Line 2020+00000016)* 006936A8 Module(sqlservr+002936A8) (CParamExchange::RestoreFromDbParams+00000588 Line 1168+0000000F)* 00692787 Module(sqlservr+00292787) (CRpcInterfaces::Relay+00000AC1 Line 814+00000000)* 00691C80 Module(sqlservr+00291C80) (CStmtExec::XretRemoteExec+00000422 Line 579+00000000)* 00674325 Module(sqlservr+00274325) (CStmtExec::XretExecute+00000465 Line 561+00000041)* 00608FD2 Module(sqlservr+00208FD2) (CMsqlExecContext::ExecuteStmts+00000560 Line 1578+00000010)* 00608657 Module(sqlservr+00208657) (CMsqlExecContext::Execute+00000368 Line 1227+0000000C)* 0063FAA0 Module(sqlservr+0023FAA0) (CSQLSource::Execute+000006D6 Line 1265+00000013)* 007DEAFE Module(sqlservr+003DEAFE) (language_exec+00000A2B Line 735+00000000)* 007E075E Module(sqlservr+003E075E) (process_commands+000001A1 Line 1765+00000009)* 04CF74BD Module(ums+000074BD) (ProcessWorkRequests+00000292 Line 449+0000000D)* 04CF6852 Module(ums+00006852) (ThreadStartRoutine+000000BD Line 263+00000007)* 77C07E53 Module(MSVCRT+00027E53) (endthread+000000AA)* 77E5D33B Module(kernel32+0001D33B) (RegisterWaitForInputIdle+00000043)
Resolution
Service pack informationTo 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/) How to obtain the latest SQL Server 2000 service pack
Hotfix informationThe English version of this hotfix 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——————————————————————-31-May-200318:452000.80.818.078,400Console.exe25-Jun-200301:012000.80.818.033,340Dbmslpcn.dll25-Apr-200302:12786,432Distmdl.ldf25-Apr-200302:122,359,296Distmdl.mdf30-Jan-200301:55180Drop_repl_hotfix.sql23-Jun-200322:402000.80.837.01,557,052Dtsui.dll23-Jun-200322:402000.80.837.0639,552Dtswiz.dll24-Apr-200302:51747,927Instdist.sql03-May-200301:561,581Inst_repl_hotfix.sql08-Feb-200306:402000.80.765.090,692Msgprox.dll01-Apr-200302:071,873Odsole.sql05-Apr-200301:462000.80.800.062,024Odsole70.dll07-May-200320:412000.80.819.025,144Opends60.dll07-May-200318:47132,096Opends60.pdb02-Apr-200321:482000.80.796.057,904Osql.exe02-Apr-200323:152000.80.797.0279,104Pfutil80.dll22-May-200322:5719,195Qfe469571.sql11-Jul-200317:041,084,147Replmerg.sql04-Apr-200321:532000.80.798.0221,768Replprov.dll08-Feb-200306:402000.80.765.0307,784Replrec.dll11-Jul-200316:561,085,925Replsys.sql01-Jun-200301:012000.80.818.0492,096Semobj.dll31-May-200318:272000.80.818.0172,032Semobj.rll29-May-200300:29115,944Sp3_serv_uni.sql01-Jun-200301:012000.80.818.04,215,360Sqldmo.dll07-Apr-200317:4425,172Sqldumper.exe19-Mar-200318:202000.80.789.028,672Sqlevn70.rll02-Jul-200300:182000.80.834.0180,736Sqlmap70.dll08-Feb-200306:402000.80.765.057,920Sqlrepss.dll25-Jul-200321:452000.80.845.07,553,105Sqlservr.exe25-Jul-200321:4512,755,968Sqlservr.pdb25-Jul-200321:442000.80.845.0590,396Sqlsort.dll08-Feb-200306:402000.80.765.045,644Sqlvdi.dll25-Jun-200301:012000.80.818.033,340Ssmslpcn.dll01-Jun-200301:012000.80.818.082,492Ssnetlib.dll01-Jun-200301:012000.80.818.025,148Ssnmpn70.dll01-Jun-200301:012000.80.818.0158,240Svrnetcn.dll31-May-200318:592000.80.818.076,416Svrnetcn.exe30-Apr-200323:522000.80.816.045,132Ums.dll30-Apr-200323:52132,096Ums.pdb02-Jul-200300:192000.80.834.098,816Xpweb70.dllNote Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

Symptoms
In Microsoft SQL Server, you use a TRY…CATCH construct to process exceptions that are raised from the DBCC CHECKDB statement. However, when the DBCC CHECKDB statement detects a consistency error in the database, the CATCH block is not triggered.
Note This behavior also occurs in SQL Server 2008.
Resolution
This behavior occurs because the DBCC CHECKDB statement does not raise an exception when the DBCC CHECKDB statement detects a consistency error inside the database.

Symptoms
Running CHECKDB against a database with at least 122,000 objects, causes a stack overflow exception. The number of objects is represented by the count of rows in SYSOBJECTS, NOT the number of tables in the database.
The stack overflow exception may cause Microsoft SQL Server to shutdown. If it does the client will receive a message that the connection has been broken. If the exception does not shutdown Microsoft SQL Server, the client will appear to be still running the DBCC.

Resolution
Instead of using CHECKDB, use a script to run DBCC CHECKALLOC as well as a loop that executes DBCC CHECKTABLE for all system and user tables. Following is a script that performs this task, that can be scheduled and run using ISQL:

DBCC CHECKALLOCGOdeclare @tabname sysnamedeclare @exec_string varchar(300)declare tabcr cursor forselect name from sysobjects where type = ‘S’ or type = ‘U’ order by nameopen tabcrfetch tabcr into @tabnameselect @exec_string = “dbcc checktable(‘” + @tabname + “‘)”select @exec_string = rtrim(@exec_string)exec(@exec_string)while (@@fetch_status = 0)begin fetch tabcr into @tabname select @exec_string = “dbcc checktable(‘” + @tabname + “‘)” exec(@exec_string)endclose tabcrdeallocate tabcr

Symptoms
You may receive an access violation exception if all the following conditions are true:You connect to a linked SQL server by using remote access.You run the DBCC FREEPROCCACHE Transact-SQL statement on the linked server to remove all elements from the procedure cache.The remote access connection is interrupted when you remove all elements from the procedure cache.You may notice the following short stack dump file that corresponds to the access violation:

* Short Stack Dump* 004AE996 Module(sqlservr+000AE996) (CLob::DestroyDeep+00000026 Line 907+0000000F)* 008CB71E Module(sqlservr+004CB71E) (CXVariant::ClearDeep+00000116 Line 4519+00000000)* 004A4D82 Module(sqlservr+000A4D82) (CXVariant::Clear+00000022 Line 325+00000000)* 004C6F66 Module(sqlservr+000C6F66) (CAutoClearXVariant::~CAutoClearXVariant+00000016 Line 2020+00000016)* 006936A8 Module(sqlservr+002936A8) (CParamExchange::RestoreFromDbParams+00000588 Line 1168+0000000F)* 00692787 Module(sqlservr+00292787) (CRpcInterfaces::Relay+00000AC1 Line 814+00000000)* 00691C80 Module(sqlservr+00291C80) (CStmtExec::XretRemoteExec+00000422 Line 579+00000000)* 00674325 Module(sqlservr+00274325) (CStmtExec::XretExecute+00000465 Line 561+00000041)* 00608FD2 Module(sqlservr+00208FD2) (CMsqlExecContext::ExecuteStmts+00000560 Line 1578+00000010)* 00608657 Module(sqlservr+00208657) (CMsqlExecContext::Execute+00000368 Line 1227+0000000C)* 0063FAA0 Module(sqlservr+0023FAA0) (CSQLSource::Execute+000006D6 Line 1265+00000013)* 007DEAFE Module(sqlservr+003DEAFE) (language_exec+00000A2B Line 735+00000000)* 007E075E Module(sqlservr+003E075E) (process_commands+000001A1 Line 1765+00000009)* 04CF74BD Module(ums+000074BD) (ProcessWorkRequests+00000292 Line 449+0000000D)* 04CF6852 Module(ums+00006852) (ThreadStartRoutine+000000BD Line 263+00000007)* 77C07E53 Module(MSVCRT+00027E53) (endthread+000000AA)* 77E5D33B Module(kernel32+0001D33B) (RegisterWaitForInputIdle+00000043)
Resolution
Service pack informationTo 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/) How to obtain the latest SQL Server 2000 service pack
Hotfix informationThe English version of this hotfix 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——————————————————————-31-May-200318:452000.80.818.078,400Console.exe25-Jun-200301:012000.80.818.033,340Dbmslpcn.dll25-Apr-200302:12786,432Distmdl.ldf25-Apr-200302:122,359,296Distmdl.mdf30-Jan-200301:55180Drop_repl_hotfix.sql23-Jun-200322:402000.80.837.01,557,052Dtsui.dll23-Jun-200322:402000.80.837.0639,552Dtswiz.dll24-Apr-200302:51747,927Instdist.sql03-May-200301:561,581Inst_repl_hotfix.sql08-Feb-200306:402000.80.765.090,692Msgprox.dll01-Apr-200302:071,873Odsole.sql05-Apr-200301:462000.80.800.062,024Odsole70.dll07-May-200320:412000.80.819.025,144Opends60.dll07-May-200318:47132,096Opends60.pdb02-Apr-200321:482000.80.796.057,904Osql.exe02-Apr-200323:152000.80.797.0279,104Pfutil80.dll22-May-200322:5719,195Qfe469571.sql11-Jul-200317:041,084,147Replmerg.sql04-Apr-200321:532000.80.798.0221,768Replprov.dll08-Feb-200306:402000.80.765.0307,784Replrec.dll11-Jul-200316:561,085,925Replsys.sql01-Jun-200301:012000.80.818.0492,096Semobj.dll31-May-200318:272000.80.818.0172,032Semobj.rll29-May-200300:29115,944Sp3_serv_uni.sql01-Jun-200301:012000.80.818.04,215,360Sqldmo.dll07-Apr-200317:4425,172Sqldumper.exe19-Mar-200318:202000.80.789.028,672Sqlevn70.rll02-Jul-200300:182000.80.834.0180,736Sqlmap70.dll08-Feb-200306:402000.80.765.057,920Sqlrepss.dll25-Jul-200321:452000.80.845.07,553,105Sqlservr.exe25-Jul-200321:4512,755,968Sqlservr.pdb25-Jul-200321:442000.80.845.0590,396Sqlsort.dll08-Feb-200306:402000.80.765.045,644Sqlvdi.dll25-Jun-200301:012000.80.818.033,340Ssmslpcn.dll01-Jun-200301:012000.80.818.082,492Ssnetlib.dll01-Jun-200301:012000.80.818.025,148Ssnmpn70.dll01-Jun-200301:012000.80.818.0158,240Svrnetcn.dll31-May-200318:592000.80.818.076,416Svrnetcn.exe30-Apr-200323:522000.80.816.045,132Ums.dll30-Apr-200323:52132,096Ums.pdb02-Jul-200300:192000.80.834.098,816Xpweb70.dllNote Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

Symptoms
Consider the following scenario: You restore a Microsoft SQL Server 2008 or SQL Server 2005 database from a backup. You receiveerrors during the restore process that prevent you from restoring the database.You successfully restore the database from the same backup by using the CONTINUE_AFTER_ERRORoption. In this scenario, when you run the DBCC CHECKDB command on the computerthat contains the SQL Server database, you receive an error message that resembles the following:

Msg 8967, Level 16, State 216, Server <server name>, Line 2
An internal error occurred in DBCC which prevented further processing. Please contact Customer Support.
DBCC results for ‘<database name>’.
Msg 8921, Level 16, State 1, Server <server name>, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.Additionally, a message that resembles the following may be displayed in the SQL Server error log:

2007-05-26 07:13:49.21 spid58DBCC encountered a page with an LSN greater than the current end of log LSN (<LSN>) for its internal database snapshot. Could not read page (file id:page id), database ‘<database name’ (database ID database id>), LSN = (<LSN>), type = 32, isInSparseFile = 1.Please re-runthis DBCC command.
Resolution
This problem occurs if the DBCC CHECKDB command cannot perform the necessary checks to confirm the consistency of the database.These checks could not be performed for many reasons. For example, this behavior may occur if there are fundamental inconsistencies in the database, such as metadata inconsistencies ordatabase snapshot corruption. More information about the specific cause of this error can be determined by examining the different state that is displayed in the error message.In the scenario that is described in the “Symptoms” section, the state 216 message indicates that the DBCC CHECKDB command read a page from the internal snapshot that has a larger log sequence number (LSN) than the end of log LSN. This behavior might occur if you restore databases by using the CONTINUE_AFTER_ERROR option.