SQL Server Performance Forum – Threads Archive

error when running query on sys.dm_db_index_physic

Hi, I’m getting the following error when running the code below (to search for fragmented indexes), any ideas ———————————————- DECLARE @DBID INT
DECLARE @SQLText nvarchar(4000) SELECT @DBID = MIN(database_id) FROM sys.databases
WHERE state = 0 AND user_access = 0 AND is_read_only = 0 AND is_in_standby = 0
AND lower(name) NOT IN (‘master’,’model’,’msdb’,’pubs’,’tempdb’,’northwind’) WHILE @DBID IS NOT NULL
BEGIN SELECT @SQLText = ‘USE [‘+DB_NAME(@DBID) +’]’+ CHAR(10)+
‘SELECT DB_NAME() AS DatabaseName, OBJECT_NAME(i.object_id) AS TableName,’+CHAR(10)+
‘i.name AS TableIndexName, ips.avg_fragmentation_in_percent’+CHAR(10)+
‘FROM sys.dm_db_index_physical_stats (‘+CONVERT(NVARCHAR(5),@DBID)+’, NULL, NULL, NULL, ”DETAILED”) ips’+CHAR(10)+
‘JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id’ EXEC sp_executesql @SQLText SELECT @DBID = MIN(database_id) FROM sys.databases
WHERE state = 0 AND user_access = 0 AND is_read_only = 0 AND is_in_standby = 0
AND lower(name) NOT IN (‘masters’,’model’,’msdb’,’pubs’,’tempdb’,’northwind’)
AND database_id > @DBID END
—————————————————————-
(227 row(s) affected)
Location: qxcntxt.cpp:954
Expression: !"No exceptions should be raised by this code"
SPID: 54
Process ID: 5652
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

What is the level of service pack on SQL Server? Refer tohttp://www.sql-server-performance.com/sj_detect_fragmentation.asp fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

9.00.2047.00
sql server 2005 sp1

actually i tried running the script above again and it unexpectedly stopped my sql server service
maybe it’s something more serious that i should report to MS?
fortunately it’s a QC server and not PROD

Any information on event viewer & sql error logs? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

The code you posted runs fine at my side. May be as you said, you should report this to MS. The message "No exceptions should be raised by this code", reminds me of an optimistic developer [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Roji. P. Thomas http://toponewithties.blogspot.com</a>

looks to be something for MS to look at? this from sql agent dump ————————————- 2006-10-24 11:23:35.98 spid52 Using ‘dbghelp.dll’ version ‘4.0.5’ 2006-10-24 11:23:36.02 spid52 ***Stack Dump being sent to d:MSSQLLOGSQLDump0005.txt 2006-10-24 11:23:36.02 spid52 SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2006-10-24 11:23:36.02 spid52 * ******************************************************************************* 2006-10-24 11:23:36.02 spid52 * 2006-10-24 11:23:36.02 spid52 * BEGIN STACK DUMP: 2006-10-24 11:23:36.02 spid52 * 10/24/06 11:23:36 spid 52 2006-10-24 11:23:36.02 spid52 * 2006-10-24 11:23:36.02 spid52 * 2006-10-24 11:23:36.02 spid52 * Exception Address = 4012AC02 Module(UNKNOWN+00000000) 2006-10-24 11:23:36.02 spid52 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION 2006-10-24 11:23:36.02 spid52 * Access Violation occurred writing address 4012AC02 2006-10-24 11:23:36.02 spid52 * Input Buffer 510 bytes - 2006-10-24 11:23:36.02 spid52 * DECLARE @DBID INT DECLARE @SQLText nvarchar(4000) SELECT 2006-10-24 11:23:36.02 spid52 * @DBID = MIN(database_id) FROM sys.databases WHERE state = 0 AND user_a 2006-10-24 11:23:36.02 spid52 * ccess = 0 AND is_read_only = 0 AND is_in_standby = 0 AND lower(name) NO 2006-10-24 11:23:36.02 spid52 * T IN (‘master’,’model’,’msdb’,’pubs’,’tempdb’,’northwind’) WHILE @DBI 2006-10-24 11:23:36.02 spid52 * D IS NOT NULL BEGIN SELECT @SQLText = ‘USE [‘+DB_NAME(@DBID) +’]’+ 2006-10-24 11:23:36.02 spid52 * CHAR(10)+ ‘SELECT DB_NAME() AS DatabaseName, OBJECT_NAME(i.object_id) 2006-10-24 11:23:36.02 spid52 * AS TableName,’+CHAR(10)+ ‘i.name AS TableIndexName, ips.avg_fragmentat 2006-10-24 11:23:36.02 spid52 * ion_in_percent’+CHAR(10)+ ‘FROM sys.dm_db_index_physical_stats (‘+CONV 2006-10-24 11:23:36.02 spid52 * ERT(NVARCHAR(5),@DBID)+’, NULL, NULL, NULL, ”DETAILED”) ips’+CHAR(10)+ 2006-10-24 11:23:36.02 spid52 * ‘JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = i 2006-10-24 11:23:36.02 spid52 * ps.index_id’ –WHERE phystat.avg_fragmentation_in_percent > 10 AND phy 2006-10-24 11:23:36.02 spid52 * stat.avg_fragmentation_in_percent < 40 –PRINT @SQLText EXEC sp_ex 2006-10-24 11:23:36.02 spid52 * ecutesql @SQLText SELECT @DBID = MIN(database_id) FROM sys.databases 2006-10-24 11:23:36.02 spid52 * WHERE state = 0 AND user_access = 0 AND is_read_only = 0 AND is_in_s 2006-10-24 11:23:36.02 spid52 * tandby = 0 AND lower(name) NOT IN (‘master’,’model’,’msdb’,’pubs’,’tem 2006-10-24 11:23:36.02 spid52 * pdb’,’northwind’) AND database_id > @DBID END 2006-10-24 11:23:36.02 spid52 * 2006-10-24 11:23:36.02 spid52 * 2006-10-24 11:23:36.02 spid52 * MODULE BASE END SIZE 2006-10-24 11:23:36.03 spid52 * sqlservr 01000000 02BCEFFF 01bcf000 2006-10-24 11:23:36.03 spid52 * ntdll 7C800000 7C8BFFFF 000c0000 2006-10-24 11:23:36.03 spid52 * kernel32 77E40000 77F41FFF 00102000 2006-10-24 11:23:36.03 spid52 * MSVCR80 78130000 781CAFFF 0009b000 2006-10-24 11:23:36.03 spid52 * msvcrt 77BA0000 77BF9FFF 0005a000 2006-10-24 11:23:36.03 spid52 * MSVCP80 7C420000 7C4A6FFF 00087000 2006-10-24 11:23:36.03 spid52 * ADVAPI32 77F50000 77FEBFFF 0009c000 2006-10-24 11:23:36.03 spid52 * RPCRT4 77C50000 77CEEFFF 0009f000 2006-10-24 11:23:36.03 spid52 * USER32 77380000 77411FFF 00092000 2006-10-24 11:23:36.03 spid52 * GDI32 77C00000 77C47FFF 00048000 2006-10-24 11:23:36.03 spid52 * CRYPT32 761B0000 76242FFF 00093000 2006-10-24 11:23:36.03 spid52 * MSASN1 76190000 761A1FFF 00012000 2006-10-24 11:23:36.03 spid52 * Secur32 76F50000 76F62FFF 00013000 2006-10-24 11:23:36.03 spid52 * MSWSOCK 71B20000 71B60FFF 00041000 2006-10-24 11:23:36.03 spid52 * WS2_32 71C00000 71C16FFF 00017000 2006-10-24 11:23:36.03 spid52 * WS2HELP 71BF0000 71BF7FFF 00008000 2006-10-24 11:23:36.03 spid52 * USERENV 76920000 769E3FFF 000c4000 2006-10-24 11:23:36.03 spid52 * opends60 333E0000 333E6FFF 00007000 2006-10-24 11:23:36.03 spid52 * NETAPI32 71C40000 71C97FFF 00058000 2006-10-24 11:23:36.03 spid52 * SHELL32 7C8D0000 7D0D2FFF 00803000 2006-10-24 11:23:36.03 spid52 * SHLWAPI 77DA0000 77DF1FFF 00052000 2006-10-24 11:23:36.03 spid52 * comctl32 77420000 77522FFF 00103000 2006-10-24 11:23:36.03 spid52 * psapi 76B70000 76B7AFFF 0000b000 2006-10-24 11:23:36.03 spid52 * instapi 48060000 48069FFF 0000a000 2006-10-24 11:23:36.03 spid52 * sqlevn70 4F610000 4F7A3FFF 00194000 2006-10-24 11:23:36.03 spid52 * SQLOS 344D0000 344D4FFF 00005000 2006-10-24 11:23:36.03 spid52 * rsaenh 68000000 6802EFFF 0002f000 2006-10-24 11:23:36.03 spid52 * AUTHZ 76C40000 76C53FFF 00014000 2006-10-24 11:23:36.03 spid52 * MSCOREE 34180000 341C4FFF 00045000 2006-10-24 11:23:36.03 spid52 * ole32 77670000 777A3FFF 00134000 2006-10-24 11:23:36.03 spid52 * msv1_0 76C90000 76CB6FFF 00027000 2006-10-24 11:23:36.03 spid52 * iphlpapi 76CF0000 76D09FFF 0001a000 2006-10-24 11:23:36.03 spid52 * kerberos 343F0000 34447FFF 00058000 2006-10-24 11:23:36.03 spid52 * cryptdll 766E0000 766EBFFF 0000c000 2006-10-24 11:23:36.03 spid52 * schannel 76750000 76776FFF 00027000 2006-10-24 11:23:36.03 spid52 * COMRES 77010000 770D5FFF 000c6000 2006-10-24 11:23:36.03 spid52 * XOLEHLP 344B0000 344B5FFF 00006000 2006-10-24 11:23:36.03 spid52 * MSDTCPRX 61B20000 61B97FFF 00078000 2006-10-24 11:23:36.03 spid52 * msvcp60 780C0000 78120FFF 00061000 2006-10-24 11:23:36.03 spid52 * MTXCLU 61BA0000 61BB8FFF 00019000 2006-10-24 11:23:36.03 spid52 * VERSION 77B90000 77B97FFF 00008000 2006-10-24 11:23:36.03 spid52 * WSOCK32 71BB0000 71BB8FFF 00009000 2006-10-24 11:23:36.03 spid52 * OLEAUT32 77D00000 77D8BFFF 0008c000 2006-10-24 11:23:36.03 spid52 * CLUSAPI 61BC0000 61BD1FF F 00012000 2006-10-24 11:23:36.03 spid52 * RESUTILS 61BE0000 61BF2FFF 00013000 2006-10-24 11:23:36.03 spid52 * DNSAPI 76ED0000 76EF8FFF 00029000 2006-10-24 11:23:36.03 spid52 * winrnr 76F70000 76F76FFF 00007000 2006-10-24 11:23:36.03 spid52 * WLDAP32 76F10000 76F3DFFF 0002e000 2006-10-24 11:23:36.03 spid52 * rasadhlp 76F80000 76F84FFF 00005000 2006-10-24 11:23:36.03 spid52 * security 62020000 62023FFF 00004000 2006-10-24 11:23:36.03 spid52 * msfte 62840000 62A98FFF 00259000 2006-10-24 11:23:36.03 spid52 * dbghelp 62AB0000 62BC2FFF 00113000 2006-10-24 11:23:36.03 spid52 * WINTRUST 76BB0000 76BDAFFF 0002b000 2006-10-24 11:23:36.03 spid52 * imagehlp 76C10000 76C38FFF 00029000 2006-10-24 11:23:36.03 spid52 * dssenh 68100000 68123FFF 00024000 2006-10-24 11:23:36.03 spid52 * hnetcfg 62F50000 62FA8FFF 00059000 2006-10-24 11:23:36.03 spid52 * wshtcpip 71AE0000 71AE7FFF 00008000 2006-10-24 11:23:36.03 spid52 * NTMARTA 77E00000 77E21FFF 00022000 2006-10-24 11:23:36.03 spid52 * SAMLIB 62E90000 62E9EFFF 0000f000 2006-10-24 11:23:36.03 spid52 * ntdsapi 766F0000 76704FFF 00015000 2006-10-24 11:23:36.03 spid52 * xpsp2res 630F0000 633B4FFF 002c5000 2006-10-24 11:23:36.03 spid52 * CLBCatQ 777B0000 77832FFF 00083000 2006-10-24 11:23:36.03 spid52 * sqlncli 633C0000 635E1FFF 00222000 2006-10-24 11:23:36.03 spid52 * COMCTL32 77530000 775C6FFF 00097000 2006-10-24 11:23:36.03 spid52 * comdlg32 762B0000 762F9FFF 0004a000 2006-10-24 11:23:36.03 spid52 * SQLNCLIR 635F0000 63622FFF 00033000 2006-10-24 11:23:36.03 spid52 * msftepxy 007C0000 007D4FFF 00015000 2006-10-24 11:23:36.03 spid52 * xpstar90 64060000 640A7FFF 00048000 2006-10-24 11:23:36.03 spid52 * SQLSCM90 640F0000 640F8FFF 00009000 2006-10-24 11:23:36.03 spid52 * ODBC32 64140000 6417CFFF 0003d000 2006-10-24 11:23:36.03 spid52 * BatchParser90 641E0000 641FEFFF 0001f000 2006-10-24 11:23:36.03 spid52 * ATL80 7C630000 7C64AFFF 0001b000 2006-10-24 11:23:36.03 spid52 * odbcint 644C0000 644D6FFF 00017000 2006-10-24 11:23:36.03 spid52 * xpstar90 645A0000 645C5FFF 00026000 2006-10-24 11:23:36.03 spid52 * xpsqlbot 647A0000 647A5FFF 00006000 2006-10-24 11:23:36.03 spid52 * xplog70 64980000 6498BFFF 0000c000 2006-10-24 11:23:36.03 spid52 * xplog70 649A0000 649A2FFF 00003000 2006-10-24 11:23:36.03 spid52 * xpsmtp80 62140000 6215AFFF 0001b000 2006-10-24 11:23:36.03 spid52 * mlang 64D00000 64D93FFF 00094000 2006-10-24 11:23:36.03 spid52 * dbghelp 64FA0000 650B2FFF 00113000 2006-10-24 11:23:36.03 spid52 * 2006-10-24 11:23:36.03 spid52 * Edi: 4257E7C0: 0103C780 00000004 422AC9A8 00000002 0052004F 00000007 2006-10-24 11:23:36.03 spid52 * Esi: 58C14068: 01082E84 00000000 0D57E3E0 FFFFFFFF 099C4901 00000000 2006-10-24 11:23:36.03 spid52 * Eax: 4012AC02: 2020204C 20202020 20202020 20202020 E8202020 17000000 2006-10-24 11:23:36.03 spid52 * Ebx: 04173458: 01016070 00000000 04173E60 041733AC 027B3ADC 027B3ADC 2006-10-24 11:23:36.03 spid52 * Ecx: 4BC4C040: 01039F4B 00002000 0000004B 00000000 742B5A00 00000000 2006-10-24 11:23:36.03 spid52 * Edx: 01039F4B: 003C9C02 4012D201 4012C802 4012BE02 4012B402 4012AC02 2006-10-24 11:23:36.03 spid52 * Eip: 4012AC02: 2020204C 20202020 20202020 20202020 E8202020 17000000 2006-10-24 11:23:36.03 spid52 * Ebp: 64BFDDCC: 64BFDDDC 0124BEB8 58C14068 18459220 64BFDE04 01248AB6 2006-10-24 11:23:36.03 spid52 * SegCs: 0000001B: 2006-10-24 11:23:36.03 spid52 * EFlags: 00010246: 00790073 00740073 006D0065 00320033 0063005C 0064006D 2006-10-24 11:23:36.03 spid52 * Esp: 64BFDDBC: 01002005 4BC4C040 58C14068 58C14068 64BFDDDC 0124BEB8 2006-10-24 11:23:36.03 spid52 * SegSs: 00000023: 2006-10-24 11:23:36.03 spid52 * ******************************************************************************* 2006-10-24 11:23:36.03 spid52 * ——————————————————————————- 2006-10-24 11:23:36.03 spid52 * Short Stack Dump 2006-10-24 11:23:36.11 spid52 4012AC02 Module(UNKNOWN+00000000) 2006-10-24 11:23:36.11 spid52 01002005 Module(sqlservr+00002005) 2006-10-24 11:23:36.11 spid52 0124BEB8 Module(sqlservr+0024BEB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 2006-10-24 11:23:36.11 spid52 01248AB6 Module(sqlservr+00248AB6) 2006-10-24 11:23:36.11 spid52 0123F010 Module(sqlservr+0023F010) 2006-10-24 11:23:36.11 spid52 0124B9AC Module(sqlservr+0024B9AC) 2006-10-24 11:23:36.11 spid52 0124B9F6 Module(sqlservr+0024B9F6) 2006-10-24 11:23:36.11 spid52 0124BA1A Module(sqlservr+0024BA1A) 2006-10-24 11:23:36.11 spid52 01248AB6 Module(sqlservr+00248AB6) 2006-10-24 11:23:36.11 spid52 01248A79 Module(sqlservr+00248A79) 2006-10-24 11:23:36.11 spid52 019010EB Module(sqlservr+009010EB) 2006-10-24 11:23:36.11 spid52 0223354E Module(sqlservr+0123354E) 2006-10-24 11:23:36.11 spid52 01E37166 Module(sqlservr+00E37166) 2006-10-24 11:23:36.11 spid52 0133AD60 Module(sqlservr+0033AD60) 2006-10-24 11:23:36.11 spid52 0133D66B Module(sqlservr+0033D66B) 2006-10-24 11:23:36.11 spid52 0133B005 Module(sqlservr+0033B005) 2006-10-24 11:23:36.11 spid52 0133D780 Module(sqlservr+0033D780) 2006-10-24 11:23:36.11 spid52 0103761A Module(sqlservr+0003761A) 2006-10-24 11:23:36.11 spid52 0103464D Module(sqlservr+0003464D) 2006-10-24 11:23:36.11 spid52 010349A6 Module(sqlservr+000349A6) 2006-10-24 11:23:36.11 spid52 010251BC Module(sqlservr+000251BC) 2006-10-24 11:23:36.11 spid52 01025741 Module(sqlservr+00025741) 2006-10-24 11:23:36.11 spid52 01023E34 Module(sqlservr+00023E34) 2006-10-24 11:23:36.11 spid52 01C05ACF Module(sqlservr+00C05ACF) 2006-10-24 11:23:36.11 spid52 01C0A4CB Module(sqlservr+00C0A4CB) 2006-10-24 11:23:36.11 spid52 01C09C7A Module(sqlservr+00C09C7A) 2006-10-24 11:23:36.11 spid52 014AD3C1 Module(sqlservr+004AD3C1) 2006-10-24 11:23:36.11 spid52 0136764A Module(sqlservr+0036764A) 2006-10-24 11:23:36.11 spid52 013674C1 Module(sqlservr+003674C1) 2006-10-24 11:23:36.11 spid52 01 367356 Module(sqlservr+00367356) 2006-10-24 11:23:36.11 spid52 010251BC Module(sqlservr+000251BC) 2006-10-24 11:23:36.11 spid52 01025741 Module(sqlservr+00025741) 2006-10-24 11:23:36.11 spid52 01023E34 Module(sqlservr+00023E34) 2006-10-24 11:23:36.11 spid52 01041DD5 Module(sqlservr+00041DD5) 2006-10-24 11:23:36.11 spid52 0103DFD4 Module(sqlservr+0003DFD4) 2006-10-24 11:23:36.11 spid52 01006A96 Module(sqlservr+00006A96) 2006-10-24 11:23:36.11 spid52 01006BBC Module(sqlservr+00006BBC) 2006-10-24 11:23:36.11 spid52 01006DAB Module(sqlservr+00006DAB) 2006-10-24 11:23:36.11 spid52 01447562 Module(sqlservr+00447562) 2006-10-24 11:23:36.11 spid52 0144859B Module(sqlservr+0044859B) 2006-10-24 11:23:36.11 spid52 0144789A Module(sqlservr+0044789A) 2006-10-24 11:23:36.12 spid52 01447720 Module(sqlservr+00447720) 2006-10-24 11:23:36.12 spid52 781329AA Module(MSVCR80+000029AA) 2006-10-24 11:23:36.12 spid52 78132A36 Module(MSVCR80+00002A36) 2006-10-24 11:23:36.12 spid52 Stack Signature for the dump is 0x282C5D9C ———————————— this from windows event viewer ———————————————— SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart). For more information, see Help and Support Center at<a target="_blank" href=http://go.microsoft.com/fwlink/events.asp.>http://go.microsoft.com/fwlink/events.asp.</a> ———————————————— EventType sql90exception, P1 sqlservr.exe, P2 2005.90.2047.0, P3 443fa399, P4 sqldumper_unknown_module.dll, P5 0.0.0.0, P6 00000000, P7 0, P8 4012ac02, P9 282c5d9c, P10 NIL. For more information, see Help and Support Center at<a target="_blank" href=http://go.microsoft.com/fwlink/events.asp.>http://go.microsoft.com/fwlink/events.asp.</a> ———————————————— Bucket 11189139, bucket table 5, EventType sql90exception, P1 sqlservr.exe, P2 2005.90.2047.0, P3 443fa399, P4 sqldumper_unknown_module.dll, P5 0.0.0.0, P6 00000000, P7 0, P8 4012ac02, P9 282c5d9c, P10 NIL. For more information, see Help and Support Center at<a target="_blank" href=http://go.microsoft.com/fwlink/events.asp.>http://go.microsoft.com/fwlink/events.asp.</a> ————————————————

Might check the event viewer log too for any relevant hardware issues on the server, also run SQLDiag in order to capture the relevant information. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

will do, thanks

]]>