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
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
If you shut down SQL Server while you are monitoring any SQL Server -Procedure Cache counter in Performance Monitor (Perfmon.exe), Perfmon.exeand Winlogon.exe close with a Divide By Zero Exception error. If you runPerfmon.exe on the Windows NT console, an Unhandled Exception error inSQLCTR60.DLL appears in Perfmon.exe.
If you monitor the counter in Perfmon.exe from a client computer, theException error occurs in Winlogon.exe and a STOP: 0xc000021a (Fatal SystemError) screen appears.
In addition, the following Application event appears in Event Viewer:

Event ID: 8Source:SQLCTR60.DLLDescription: DB-LIBRARY error: Attempt to bind to a non-existent column.
Resolution
To work around this problem:Do not monitor SQL Server – Procedure Cache counters when you shut down SQL Server.
-or-Use “On Demand Mode” for SQL Server Perfmon Integration. For moreinformation, see the “Integration with Windows NT Performance Monitor”section in the SQL Server Administrator’s Companion guide.

Symptoms
When you frequently back up or restore a database in Microsoft SQL Server 2008, the memory usage increases gradually. Additionally, the size of the procedure cache grows at a steady rate.
You can see this problem by running the following statements.

SELECT p.plan_handle,CONVERT (varchar, GETDATE(), 126) AS runtime, LEFT (p.cacheobjtype + ‘ (‘ + p.objtype + ‘)’, 35) AS cacheobjtype,p.usecounts, p.size_in_bytes / 1024 AS size_in_kb, stat.total_worker_time/1000 AS tot_cpu_ms, stat.total_elapsed_time/1000 AS tot_duration_ms, stat.total_physical_reads, stat.total_logical_writes, stat.total_logical_reads, LEFT (CASE WHEN pa.value=32767 THEN ‘ResourceDb’ ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname,pa.value))END, 40) AS dbname,sql.objectid, CONVERT (nvarchar(50), CASE WHEN sql.objectid IS NULL THEN NULL ELSE REPLACE (REPLACE (sql.[text],CHAR(13), ‘ ‘), CHAR(10), ”)END) AS procname, REPLACE (REPLACE (SUBSTRING (sql.[text], stat.statement_start_offset/2 + 1, CASE WHEN stat.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), sql.[text])) ELSE stat.statement_end_offset/2 – stat.statement_start_offset/2 + 1END), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS stmt_textFROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa INNER JOIN sys.dm_exec_query_stats stat ON p.plan_handle = stat.plan_handle OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sqlWHERE pa.attribute = ‘dbid’ ORDER BY p.plan_handle DESCIn the result, you may find that there are many records in which the values of the stmt_text column resemble the following:
select @media_set_id = media_set_id from msdb.dbo.backupmediaset where media_uuid = N’{7C5DCC4D-3065-47F3-AA5F-2C498DFF27F8}
insert msdb.dbo.backupmediaset (media_uuid, media_family_count, software_name, software_vendor_id, MTF_major_version, mirror_count, is_password_protected, is_compressed) values (N’{7C5DCC4D-3065-47F3-AA5F-2C498DFF27F8}’, 1, @param0, 4608, 1, 1, 0, 1)
select @media_set_id = @@identity end select @media_count = media_count from msdb.dbo.backupmediafamily where media_set_id = @media_set_id and family_sequence_number = 1These operations are the read operations and the write operations to the msdb backup history tables.
Resolution
The procedure cache grows because the backup statements or the restore statements are not fully parameterized. Each statement results in a prepared statement in the cache that is never reused.