October 17, 2014 at 9:13 am
I ran dbcc checkdb with no_infomsgs, all_errormsgs from a scheduled job and got this in the Stack Dump file:
DBCC RESULTS
--------------------
<DbccResults>
<Dbcc ID="0" Error="8928" Severity="16" State="1">Object ID 95651734, index ID 1, partition ID 72057604988862464,
alloc unit ID 72057605012324352 (type In-row data): Page (1:5770272) could not be processed. See other errors f
or details.</Dbcc>
<Dbcc ID="1" Error="8939" Severity="16" State="98">Table error: Object ID 95651734, index ID 1, partition ID 7205
7604988862464, alloc unit ID 72057605012324352 (type In-row data), page (1:5770272). Test (IS_OFF (BUF_IOERR, pBU
F->bstat)) failed. Values are 12716041 and -4.</Dbcc>
<Dbcc ID="2" Error="8976" Severity="16" State="1">Table error: Object ID 95651734, index ID 1, partition ID 72057
604988862464, alloc unit ID 72057605012324352 (type In-row data). Page (1:5770272) was not seen in the scan altho
ugh its parent (1:7287343) and previous (1:5770127) refer to it. Check any previous errors.</Dbcc>
<Dbcc ID="3" Error="8978" Severity="16" State="1">Table error: Object ID 95651734, index ID 1, partition ID 72057
604988862464, alloc unit ID 72057605012324352 (type In-row data). Page (1:5770273) is missing a reference from pr
evious page (1:5770272). Possible chain linkage problem.</Dbcc>
<Dbcc ID="4" Error="8990" Severity="10" State="1">CHECKDB found 0 allocation errors and 4 consistency errors in t
able 'APDISTRIB' (object ID 95651734).</Dbcc>
<Dbcc ID="5" Error="8928" Severity="16" State="1">Object ID 814938275, index ID 1, partition ID 72057605271519232
, alloc unit ID 72057605294981120 (type In-row data): Page (1:516276) could not be processed. See other errors f
or details.</Dbcc>
<Dbcc ID="6" Error="8976" Severity="16" State="1">Table error: Object ID 814938275, index ID 1, partition ID 7205
7605271519232, alloc unit ID 72057605294981120 (type In-row data). Page (1:516276) was not seen in the scan altho
ugh its parent (1:14556151) and previous (1:516275) refer to it. Check any previous errors.</Dbcc>
<Dbcc ID="7" Error="8978" Severity="16" State="1">Table error: Object ID 814938275, index ID 1, partition ID 7205
7605271519232, alloc unit ID 72057605294981120 (type In-row data). Page (1:516277) is missing a reference from pr
evious page (1:516276). Possible chain linkage problem.</Dbcc>
<Dbcc ID="8" Error="8990" Severity="10" State="1">CHECKDB found 0 allocation errors and 3 consistency errors in t
able 'PAYDEDUCTN' (object ID 814938275).</Dbcc>
<Dbcc ID="9" Error="8989" Severity="10" State="1">CHECKDB found 0 allocation errors and 7 consistency errors in d
atabase 'TEST'.</Dbcc>
<Dbcc ID="10" Error="8957" Severity="-1" State="1">DBCC CHECKDB (TEST) WITH all_errormsgs, no_infomsgs executed b
y KENNEDY\sqladmin found 7 errors and repaired 0 errors. Elapsed time: 0 hours 57 minutes 14 seconds. Internal d
atabase snapshot has split point LSN = 0006452a:00000253:0001 and first LSN = 0006452a:00000252:0001.</Dbcc>
<Dbcc ID="11" Error="8958" Severity="10" State="1">repair_allow_data_loss is the minimum repair level for the err
ors found by DBCC CHECKDB (TEST).</Dbcc>
</DbccResults>
**Dump thread - spid = 0, EC = 0x00000000CE292080
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
I then ran DBCC CHECKTABLE for the two tables in the results above (APDISTRIB and PAYDEDUCTN) in Query Analyzer and both ran clean:
DBCC results for 'PAYDEDUCTN'.
There are 25095197 rows in 1411311 pages for object "PAYDEDUCTN".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC results for 'APDISTRIB'.
There are 650068 rows in 70844 pages for object "APDISTRIB".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I then ran DBCC CHECKDB again and it ran clean:
... CHECKDB found 0 allocation errors and 0 consistency errors in database 'TEST'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I'm confused. Should I still be concerned?
Thanks all.
October 17, 2014 at 9:28 am
Did anything happen between when you ran CheckDB and when you ran CheckTable? Anything like an index rebuild or similar?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2014 at 9:37 am
No. Just a couple of scheduled backups. Here's the Log. This is a test system, low activity. After the stack dump, a few backups and then me running dbccs on all the databases.
Date,Source,Severity,Message
10/17/2014 11:30:39,spid63,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.
10/17/2014 11:30:39,spid63,Unknown,Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
10/17/2014 11:27:20,spid110,Unknown,DBCC CHECKDB (msdb) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds. Internal database snapshot has split point LSN = 000006f6:00000119:0001 and first LSN = 000006f6:00000118:0001.
10/17/2014 11:27:04,spid109,Unknown,DBCC CHECKDB (model) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000042:000000bc:0001 and first LSN = 00000042:000000bb:0001.
10/17/2014 11:26:46,spid108,Unknown,DBCC CHECKDB (mssqlsystemresource) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 8 seconds.
10/17/2014 11:26:38,spid108,Unknown,DBCC CHECKDB (master) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000142:000000db:0002 and first LSN = 00000142:000000da:0001.
10/17/2014 11:26:18,spid107,Unknown,DBCC CHECKDB (TF90) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds. Internal database snapshot has split point LSN = 00000959:00000295:0001 and first LSN = 00000959:00000294:0001.
10/17/2014 11:25:56,spid106,Unknown,DBCC CHECKDB (TF10) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds. Internal database snapshot has split point LSN = 0000009a:0000021a:0001 and first LSN = 0000009a:00000219:0001.
10/17/2014 11:25:33,spid102,Unknown,DBCC CHECKDB (MSCM) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds. Internal database snapshot has split point LSN = 000002e4:00001361:0001 and first LSN = 000002e4:00001360:0001.
10/17/2014 11:25:11,spid101,Unknown,DBCC CHECKDB (LOGAN) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds. Internal database snapshot has split point LSN = 000000cd:00000c95:0001 and first LSN = 000000cd:00000c94:0001.
10/17/2014 11:24:50,spid61,Unknown,DBCC CHECKDB (GEN) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 15 seconds. Internal database snapshot has split point LSN = 00003505:00000c78:0001 and first LSN = 00003505:00000c77:0001.
10/17/2014 10:44:11,spid105,Unknown,DBCC CHECKDB (TEST) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 50 minutes 34 seconds. Internal database snapshot has split point LSN = 0006452a:00000261:0001 and first LSN = 0006452a:00000260:0001.
10/17/2014 10:19:24,spid61,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.
10/17/2014 10:19:23,spid61,Unknown,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/17/2014 10:00:15,Backup,Unknown,Log was backed up. Database: TEST<c/> creation date(time): 2012/04/25(12:11:30)<c/> first LSN: 410922:598:1<c/> last LSN: 410922:610:1<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_A2106B14-5472-4FA4-9E9D-4EA34848708C'<c/> 'SQLBACKUP_A2106B14-5472-4FA4-9E9D-4EA34848708C01'}). This is an informational message only. No user action is required.
10/17/2014 09:53:11,spid103,Unknown,DBCC CHECKTABLE (TEST.dbo.PAYDEDUCTN) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 4 minutes 32 seconds. Internal database snapshot has split point LSN = 0006452a:0000025d:0001 and first LSN = 0006452a:0000025c:0001.
10/17/2014 09:42:38,spid100,Unknown,DBCC CHECKTABLE (TEST.dbo.APDISTRIB) executed by KENNEDY\doubran found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 32 seconds. Internal database snapshot has split point LSN = 0006452a:00000259:0001 and first LSN = 0006452a:00000258:0001.
10/17/2014 08:00:01,Backup,Unknown,Log was backed up. Database: TEST<c/> creation date(time): 2012/04/25(12:11:30)<c/> first LSN: 410921:2625:1<c/> last LSN: 410922:598:1<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_A53CB57A-5268-49E4-BCBE-00EDFE8A51E8'<c/> 'SQLBACKUP_A53CB57A-5268-49E4-BCBE-00EDFE8A51E801'}). This is an informational message only. No user action is required.
10/17/2014 05:01:07,Backup,Unknown,Database backed up. Database: msdb<c/> creation date(time): 2010/04/02(17:35:08)<c/> pages dumped: 2895<c/> first LSN: 1781:470:139<c/> last LSN: 1782:17:1<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_6E6A990E-53D8-4CFD-96D2-A79E2A681EFE'<c/> 'SQLBACKUP_6E6A990E-53D8-4CFD-96D2-A79E2A681EFE01'}). This is an informational message only. No user action is required.
10/17/2014 05:01:04,Backup,Unknown,Database backed up. Database: model<c/> creation date(time): 2003/04/08(09:13:36)<c/> pages dumped: 317<c/> first LSN: 66:152:37<c/> last LSN: 66:168:1<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_ED923E0A-2996-41A9-8D80-05927AA05A02'<c/> 'SQLBACKUP_ED923E0A-2996-41A9-8D80-05927AA05A0201'}). This is an informational message only. No user action is required.
10/17/2014 05:01:02,Backup,Unknown,Database backed up. Database: master<c/> creation date(time): 2014/10/02(17:51:54)<c/> pages dumped: 578<c/> first LSN: 322:130:72<c/> last LSN: 322:160:1<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_5C5B0038-BB77-481A-854B-8EFAF493CF18'<c/> 'SQLBACKUP_5C5B0038-BB77-481A-854B-8EFAF493CF1801'}). This is an informational message only. No user action is required.
10/17/2014 05:00:59,Backup,Unknown,Database differential changes were backed up. Database: MSCM<c/> creation date(time): 2014/10/01(14:05:48)<c/> pages dumped: 609<c/> first LSN: 729:1498:115<c/> last LSN: 729:1547:1<c/> full backup LSN: 651:3730:187<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_D44A7513-CA77-4C65-BFD3-CD71829D1AB6'<c/> 'SQLBACKUP_D44A7513-CA77-4C65-BFD3-CD71829D1AB601'}). This is an informational message. No user action is required.
10/17/2014 05:00:55,Backup,Unknown,Database differential changes were backed up. Database: TF10<c/> creation date(time): 2014/09/22(13:55:47)<c/> pages dumped: 308<c/> first LSN: 154:533:1<c/> last LSN: 154:535:1<c/> full backup LSN: 154:494:37<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_5F9686DD-383F-43FB-84DE-370A1ABF4101'<c/> 'SQLBACKUP_5F9686DD-383F-43FB-84DE-370A1ABF410101'}). This is an informational message. No user action is required.
10/17/2014 05:00:52,Backup,Unknown,Database differential changes were backed up. Database: TF90<c/> creation date(time): 2012/04/25(13:54:06)<c/> pages dumped: 313<c/> first LSN: 2393:656:1<c/> last LSN: 2393:658:1<c/> full backup LSN: 2393:611:36<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_A831E4A9-EC99-4612-BB10-CA2B5CE6FD78'<c/> 'SQLBACKUP_A831E4A9-EC99-4612-BB10-CA2B5CE6FD7801'}). This is an informational message. No user action is required.
10/17/2014 05:00:45,Backup,Unknown,Database differential changes were backed up. Database: TEST<c/> creation date(time): 2012/04/25(12:11:30)<c/> pages dumped: 22346<c/> first LSN: 410922:596:1<c/> last LSN: 410922:598:1<c/> full backup LSN: 410880:16:47<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_C08378ED-FFFC-4537-B506-644DE3009B94'<c/> 'SQLBACKUP_C08378ED-FFFC-4537-B506-644DE3009B9401'}). This is an informational message. No user action is required.
10/17/2014 05:00:20,Backup,Unknown,Database differential changes were backed up. Database: LOGAN<c/> creation date(time): 2012/04/25(13:50:30)<c/> pages dumped: 332<c/> first LSN: 205:3216:1<c/> last LSN: 205:3218:1<c/> full backup LSN: 205:3164:36<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_8FC3ABF6-36D9-42DB-984F-78960D37DA17'<c/> 'SQLBACKUP_8FC3ABF6-36D9-42DB-984F-78960D37DA1701'}). This is an informational message. No user action is required.
10/17/2014 05:00:13,Backup,Unknown,Database differential changes were backed up. Database: GEN<c/> creation date(time): 2012/04/25(13:52:03)<c/> pages dumped: 747<c/> first LSN: 13573:2844:96<c/> last LSN: 13573:2877:1<c/> full backup LSN: 13572:1733:125<c/> number of dump devices: 2<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_DF35A1E0-F371-4908-BBBB-1B633F491257'<c/> 'SQLBACKUP_DF35A1E0-F371-4908-BBBB-1B633F49125701'}). This is an informational message. No user action is required.
10/17/2014 03:58:19,spid65,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
10/17/2014 03:58:00,spid65,Unknown,Stack Signature for the dump is 0x0000000000000023
10/17/2014 03:58:00,spid65,Unknown,* Short Stack Dump
10/17/2014 03:58:00,spid65,Unknown,* -------------------------------------------------------------------------------
10/17/2014 03:58:00,spid65,Unknown,* *******************************************************************************
10/17/2014 03:58:00,spid65,Unknown,*
10/17/2014 03:58:00,spid65,Unknown,* dbcc checkdb('TEST') with no_infomsgs<c/> all_errormsgs
10/17/2014 03:58:00,spid65,Unknown,* Input Buffer 126 bytes -
10/17/2014 03:58:00,spid65,Unknown,*
10/17/2014 03:58:00,spid65,Unknown,* DBCC database corruption
10/17/2014 03:58:00,spid65,Unknown,*
10/17/2014 03:58:00,spid65,Unknown,* 10/17/14 03:58:00 spid 65
10/17/2014 03:58:00,spid65,Unknown,* BEGIN STACK DUMP:
10/17/2014 03:58:00,spid65,Unknown,*
10/17/2014 03:58:00,spid65,Unknown,* *******************************************************************************
10/17/2014 03:58:00,spid65,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
10/17/2014 03:58:00,spid65,Unknown,**Dump thread - spid = 0<c/> EC = 0x00000000CE292080
10/17/2014 03:57:59,spid65,Unknown,Using 'dbghelp.dll' version '4.0.5'
10/17/2014 03:57:59,spid65,Unknown,DBCC CHECKDB (TEST) WITH all_errormsgs<c/> no_infomsgs executed by KENNEDY\sqladmin found 7 errors and repaired 0 errors. Elapsed time: 0 hours 57 minutes 14 seconds. Internal database snapshot has split point LSN = 0006452a:00000253:0001 and first LSN = 0006452a:00000252:0001.
10/17/2014 03:00:44,spid65,Unknown,DBCC CHECKDB (LOGAN) WITH all_errormsgs<c/> no_infomsgs executed by KENNEDY\sqladmin found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 2 seconds. Internal database snapshot has split point LSN = 000000cd:00000c8c:0001 and first LSN = 000000cd:00000c8b:0001.
10/17/2014 03:00:37,spid65,Unknown,DBCC CHECKDB (GEN) WITH all_errormsgs<c/> no_infomsgs executed by KENNEDY\sqladmin found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 31 seconds. Internal database snapshot has split point LSN = 00003505:00000ac7:0001 and first LSN = 00003505:00000ac6:0001.
10/17/2014 00:00:09,spid21s,Unknown,This instance of SQL Server has been using a process ID of 2712 since 10/2/2014 5:52:13 PM (local) 10/2/2014 9:52:13 PM (UTC). This is an informational message only; no user action is required.
October 17, 2014 at 9:42 am
And that's me in the log stopping the Agent. Small server. Was trying to free resources for the DBCC to complete.
October 28, 2014 at 8:17 am
I had several occurrences of this, DBCC Checkdb reporting errors that turned up false, run clean the next day. Then on one of runs reporting errors I found "Resource Monitor worker appears to be non-yielding on node 0. Memory freed xxx... etc..." messages in the log. This is a test server (VM) and only had 4GB of RAM. So I think sql server was getting paged out running the dbcc. The database is about 200GB. So I got an additional 4 for a total of 8GB ram. Two days, both clean runs. So I think the root cause is an undersized server.
But it raises another question for me.
Here's my DBCC Checkdb stored procedure, called passing in the database name (@db):
declare @subj varchar(100),
@cmd varchar(200),
@file varchar(100)
select@subj = @@servername + ' ' + @db + ' Integrity Check',
@cmd = 'dbcc checkdb(''' + @db + ''') with no_infomsgs, all_errormsgs',
@file = @db + '_dbcc.txt' ;
exec msdb.dbo.sp_send_dbmail
@profile_name = 'sqladmin',
@recipients = 'Me@MyHouse.org',
@subject = @subj,
@query = @cmd,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file ;
If I remove with no_infomsgs, all_errormsgs I'll get an email with the DBCC results. But with the consistency errors, I was getting no email from this procedure, a Job Failed message from the Agent and I would find a Short Stack Dump reported in the log. Won't my procedure return the errors in the email? I mean, if the server was properly sized and there were real CheckDB errors?
Thanks all.
October 28, 2014 at 8:25 am
I guess I mean: Wasn't I getting the Short Stack Dump because SQL Server was having trouble running the Checkdb, not that the Checkdb was reporting consistency errors? And that if SQL Server was able to smoothly run Checkdb and found an issue, it should be reported in an email from my procedure?
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply