CHECKDB Error. Msg 0, Level 11, State 0, Line 0. What to do?

dawave · March 8, 2017, 10:24pm

Hi, Sir

Our Maintenance Plan keeps on failing and we found that it has these errors. (MS SQL 2012)

Tried::
USE [DB]
GO
DBCC CHECKDB(N'DBTABLE') WITH NO_INFOMSGS, ALL_ERRORMSGS
GO

Result::
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.

I was wondering if there is a way to fix it besides restoring from previous backup? So we can minimize the data loss since the last backup was done 4 months ago..................

Thank you for your time and any feedback is greatly appreciated.

satyakrishna · March 9, 2017, 3:36am

How big is your database? How long does the job execute for before it fails?

Kristen · March 9, 2017, 8:31am

Dear oh deary me ...

Is

A severe error occurred on the current command. The results, if any, should be discarded.

the ONLY output you get? Nothing with any different detail?

ScottPletcher · March 9, 2017, 3:22pm

You need to look at the SQL Server error log. There should be something else in there.

Run a check on the entire db rather than on just the table.

jeffw8713 · March 9, 2017, 6:57pm

You can get this kind of error if you don't have enough space on the volume to allow the database snapshot to be created. Verify your drive has enough space available.

Also - you probably want to attempt to backup the database now. If it fails you can try the option to continue on error...this will at least get you something more current. If it works without error then you at least have a good point to restore - if it only works with the continue on error at least you may have something to restore to with a lot less than 4 months of data loss.

dawave · March 9, 2017, 11:43pm

Hi, Team

The DB is about 4-5GB big. It probably took less than 10 secs for the errors to pop up when running CHECKDB.

The Maintenance Plan kept on failing when backing up all 6 DBs. So I ran CHECKDB on each DB individually and notice that THIS ONE DB is causing the error.

dawave · March 9, 2017, 11:45pm

I verified that we still have plenty of space on the HDD yes.

I did backup the DB already but with the CHECKDB failing, I am worried that Backup's integrity will be bad.

dawave · March 9, 2017, 11:50pm

Any suggestion??

Date,Source,Severity,Message
03/09/2017 15:40:08,spid70,Unknown,DBCC CHECKDB (DBUp) WITH all_errormsgs no_infomsgs executed by sa terminated abnormally due to error state 5. Elapsed time: 0 hours 0 minutes 1 seconds.
03/09/2017 15:40:08,spid70,Unknown,External dump process return code 0x20000001.External dump process returned no errors.
03/09/2017 15:40:07,spid70,Unknown,Stack Signature for the dump is 0x8AF344E9
03/09/2017 15:40:07,spid70,Unknown,77DB15D6 Module(ntdll+000415D6)
03/09/2017 15:40:07,spid70,Unknown,77DB1603 Module(ntdll+00041603)
03/09/2017 15:40:07,spid70,Unknown,7647D2E9 Module(kernel32+0004D2E9)
03/09/2017 15:40:07,spid70,Unknown,7013C600 Module(MSVCR100+0005C600)
03/09/2017 15:40:07,spid70,Unknown,7013C556 Module(MSVCR100+0005C556)
03/09/2017 15:40:07,spid70,Unknown,6B03F5BA Module(sqldk+0000F5BA)
03/09/2017 15:40:07,spid70,Unknown,6B03F05B Module(sqldk+0000F05B)
03/09/2017 15:40:07,spid70,Unknown,6B03F26A Module(sqldk+0000F26A)
03/09/2017 15:40:07,spid70,Unknown,6B03F36D Module(sqldk+0000F36D)
03/09/2017 15:40:07,spid70,Unknown,6B069954 Module(sqldk+00039954)
03/09/2017 15:40:07,spid70,Unknown,6B069C2E Module(sqldk+00039C2E)
03/09/2017 15:40:07,spid70,Unknown,6B0697FA Module(sqldk+000397FA)
03/09/2017 15:40:07,spid70,Unknown,69B6F234 Module(sqllang+0041F234)
03/09/2017 15:40:07,spid70,Unknown,69B6F881 Module(sqllang+0041F881)
03/09/2017 15:40:07,spid70,Unknown,69B6B462 Module(sqllang+0041B462)
03/09/2017 15:40:07,spid70,Unknown,69B6BC44 Module(sqllang+0041BC44)
03/09/2017 15:40:07,spid70,Unknown,69B6AA2C Module(sqllang+0041AA2C)
03/09/2017 15:40:07,spid70,Unknown,6996AF33 Module(sqllang+0021AF33)
03/09/2017 15:40:07,spid70,Unknown,6A3A1308 Module(sqllang+00C51308)
03/09/2017 15:40:07,spid70,Unknown,6A312DF7 Module(sqllang+00BC2DF7)
03/09/2017 15:40:07,spid70,Unknown,6CCFC00D Module(sqlmin+0111C00D)
03/09/2017 15:40:07,spid70,Unknown,6CCFD46F Module(sqlmin+0111D46F)
03/09/2017 15:40:07,spid70,Unknown,6CCEBD13 Module(sqlmin+0110BD13)
03/09/2017 15:40:07,spid70,Unknown,6CCE5F9F Module(sqlmin+01105F9F)
03/09/2017 15:40:07,spid70,Unknown,6CCDAB5C Module(sqlmin+010FAB5C)
03/09/2017 15:40:07,spid70,Unknown,69B5CD9C Module(sqllang+0040CD9C)
03/09/2017 15:40:07,spid70,Unknown,69B5CD49 Module(sqllang+0040CD49)
03/09/2017 15:40:07,spid70,Unknown,69B5CB0F Module(sqllang+0040CB0F)
03/09/2017 15:40:07,spid70,Unknown,69B5BD35 Module(sqllang+0040BD35)
03/09/2017 15:40:07,spid70,Unknown,69B6B462 Module(sqllang+0041B462)
03/09/2017 15:40:07,spid70,Unknown,69B6BC44 Module(sqllang+0041BC44)
03/09/2017 15:40:07,spid70,Unknown,69B6AA2C Module(sqllang+0041AA2C)
03/09/2017 15:40:07,spid70,Unknown,69B75C55 Module(sqllang+00425C55)
03/09/2017 15:40:07,spid70,Unknown,69B750C3 Module(sqllang+004250C3)
03/09/2017 15:40:07,spid70,Unknown,69B74D9C Module(sqllang+00424D9C)
03/09/2017 15:40:07,spid70,Unknown,69B3BD05 Module(sqllang+003EBD05)
03/09/2017 15:40:07,spid70,Unknown,6BDD90A0 Module(sqlmin+001F90A0)
03/09/2017 15:40:07,spid70,Unknown,6BDDEACF Module(sqlmin+001FEACF)
03/09/2017 15:40:07,spid70,Unknown,6BDDEB41 Module(sqlmin+001FEB41)
03/09/2017 15:40:07,spid70,Unknown,6BDDE63D Module(sqlmin+001FE63D)
03/09/2017 15:40:07,spid70,Unknown,6BDDA771 Module(sqlmin+001FA771)
03/09/2017 15:40:07,spid70,Unknown,6BDDA5D9 Module(sqlmin+001FA5D9)
03/09/2017 15:40:07,spid70,Unknown,6C5D3D1E Module(sqlmin+009F3D1E)
03/09/2017 15:40:07,spid70,Unknown,6A68F7FF Module(sqllang+00F3F7FF)
03/09/2017 15:40:07,spid70,Unknown,6CD40BAF Module(sqlmin+01160BAF)
03/09/2017 15:40:07,spid70,Unknown,6CCDA0CD Module(sqlmin+010FA0CD)
03/09/2017 15:40:07,spid70,Unknown,6CCE7163 Module(sqlmin+01107163)
03/09/2017 15:40:07,spid70,Unknown,6CCE906E Module(sqlmin+0110906E)
03/09/2017 15:40:07,spid70,Unknown,6BCA69E2 Module(sqlmin+000C69E2)
03/09/2017 15:40:07,spid70,Unknown,6BCA6B34 Module(sqlmin+000C6B34)
03/09/2017 15:40:07,spid70,Unknown,6BCA5F48 Module(sqlmin+000C5F48)
03/09/2017 15:40:07,spid70,Unknown,6BCA8C9F Module(sqlmin+000C8C9F)
03/09/2017 15:40:07,spid70,Unknown,* Short Stack Dump
03/09/2017 15:40:07,spid70,Unknown,* -------------------------------------------------------------------------------
03/09/2017 15:40:07,spid70,Unknown,* *******************************************************************************
03/09/2017 15:40:07,spid70,Unknown,* SegSs: 00000023:
03/09/2017 15:40:07,spid70,Unknown,* Esp: 0FA9A3D4: 757A561B 21482414 21482408 00000000 00000000 00000000
03/09/2017 15:40:07,spid70,Unknown,* EFlags: 00010246: 00000000 00000000 00000000 00000000 00000000 00000000
03/09/2017 15:40:07,spid70,Unknown,* SegCs: 0000001B:
03/09/2017 15:40:07,spid70,Unknown,* Ebp: 0FA9A4B4: 0FA9A520 6BCA5F48 21482414 214826DC 21482408 00000010
03/09/2017 15:40:07,spid70,Unknown,* Eip: 6BCA8C9F: 4D8D108B C88B51C0 8B0452FF FFFF6885 08EC83FF 0189CC8B
03/09/2017 15:40:07,spid70,Unknown,* Edx: 0FA9A410: 20B02028 5F080290 20B02E98 00000000 00000000 00000000
03/09/2017 15:40:07,spid70,Unknown,* Ecx: 757A5163:
03/09/2017 15:40:07,spid70,Unknown,* Ebx: 21482408: 6BCA7598 21483FF0 00000000 0000020D FFFFFFFF FFFFFFFF
03/09/2017 15:40:07,spid70,Unknown,* Eax: 00000000:
03/09/2017 15:40:07,spid70,Unknown,* Esi: 21482408: 6BCA7598 21483FF0 00000000 0000020D FFFFFFFF FFFFFFFF
03/09/2017 15:40:07,spid70,Unknown,* Edi: 20B02DF8: 6BCA74B4 6BCA74B0 0FA9A410 00000000 96520000 01000000
03/09/2017 15:40:07,spid70,Unknown,*
03/09/2017 15:40:07,spid70,Unknown,* dbghelp 40790000 408D0FFF 00141000
03/09/2017 15:40:07,spid70,Unknown,* rasadhlp 74700000 74705FFF 00006000
03/09/2017 15:40:07,spid70,Unknown,* winrnr 742F0000 742F7FFF 00008000
03/09/2017 15:40:07,spid70,Unknown,* napinsp 74300000 7430EFFF 0000f000
03/09/2017 15:40:07,spid70,Unknown,* dhcpcsvc6 75A40000 75A61FFF 00022000
03/09/2017 15:40:07,spid70,Unknown,* WINNSI 75E20000 75E26FFF 00007000
03/09/2017 15:40:07,spid70,Unknown,* dhcpcsvc 75C30000 75C64FFF 00035000
03/09/2017 15:40:07,spid70,Unknown,* IPHLPAPI 75E30000 75E48FFF 00019000
03/09/2017 15:40:07,spid70,Unknown,* NLAapi 75640000 7564EFFF 0000f000
03/09/2017 15:40:07,spid70,Unknown,* apphelp 76260000 7628BFFF 0002c000
03/09/2017 15:40:07,spid70,Unknown,* msxmlsql 5ADE0000 5AEBBFFF 000dc000
03/09/2017 15:40:07,spid70,Unknown,* xplog70 5D110000 5D112FFF 00003000
03/09/2017 15:40:07,spid70,Unknown,* xplog70 5D3F0000 5D3FDFFF 0000e000
03/09/2017 15:40:07,spid70,Unknown,* xpstar 5D020000 5D044FFF 00025000
03/09/2017 15:40:07,spid70,Unknown,* odbcint 619D0000 61A07FFF 00038000
03/09/2017 15:40:07,spid70,Unknown,* ODBC32 61BD0000 61C35FFF 00066000
03/09/2017 15:40:07,spid70,Unknown,* SQLSCM 61E80000 61E8AFFF 0000b000
03/09/2017 15:40:07,spid70,Unknown,* ATL100 724D0000 724F5FFF 00026000
03/09/2017 15:40:07,spid70,Unknown,* xpstar 5CD10000 5CD5DFFF 0004e000
03/09/2017 15:40:07,spid70,Unknown,* xpsqlbot 5FB30000 5FB36FFF 00007000
03/09/2017 15:40:07,spid70,Unknown,* wshtcpip 75700000 75704FFF 00005000
03/09/2017 15:40:07,spid70,Unknown,* wship6 75A70000 75A74FFF 00005000
03/09/2017 15:40:07,spid70,Unknown,* mswsock 75A80000 75ABAFFF 0003b000
03/09/2017 15:40:07,spid70,Unknown,* dssenh 74710000 74735FFF 00026000
03/09/2017 15:40:07,spid70,Unknown,* dbghelp 61E90000 61FD0FFF 00141000
03/09/2017 15:40:07,spid70,Unknown,* MSFTE 49910000 49B68FFF 00259000
03/09/2017 15:40:07,spid70,Unknown,* ftimport 60000000 6001CFFF 0001d000
03/09/2017 15:40:07,spid70,Unknown,* BatchParser 64110000 64131FFF 00022000
03/09/2017 15:40:07,spid70,Unknown,* clrjit 73FB0000 7401DFFF 0006e000
03/09/2017 15:40:07,spid70,Unknown,* nlssorting 72A60000 72A71FFF 00012000
03/09/2017 15:40:07,spid70,Unknown,* culture 73AF0000 73AFCFFF 0000d000
03/09/2017 15:40:07,spid70,Unknown,* mscorlib.ni 70B20000 71ADEFFF 00fbf000
03/09/2017 15:40:07,spid70,Unknown,* MSVCR110_CLR0400 74020000 740F2FFF 000d3000
03/09/2017 15:40:07,spid70,Unknown,* clr 72AA0000 73131FFF 00692000
03/09/2017 15:40:07,spid70,Unknown,* SQLNCLIR11 63870000 638A7FFF 00038000
03/09/2017 15:40:07,spid70,Unknown,* COMDLG32 77600000 77672FFF 00073000
03/09/2017 15:40:07,spid70,Unknown,* COMCTL32 745A0000 74624FFF 00085000
03/09/2017 15:40:07,spid70,Unknown,* sqlncli11 63220000 634FDFFF 002de000
03/09/2017 15:40:07,spid70,Unknown,* CLBCatQ 777A0000 77823FFF 00084000
03/09/2017 15:40:07,spid70,Unknown,* security 742E0000 742E2FFF 00003000
03/09/2017 15:40:07,spid70,Unknown,* RESUTILS 65B70000 65B82FFF 00013000
03/09/2017 15:40:07,spid70,Unknown,* ATL 75000000 75013FFF 00014000
03/09/2017 15:40:07,spid70,Unknown,* credui 6E1D0000 6E1FDFFF 0002e000
03/09/2017 15:40:07,spid70,Unknown,* adsldpc 69050000 69082FFF 00033000
03/09/2017 15:40:07,spid70,Unknown,* ACTIVEDS 6DD30000 6DD64FFF 00035000
03/09/2017 15:40:07,spid70,Unknown,* DNSAPI 75ED0000 75EFBFFF 0002c000
03/09/2017 15:40:07,spid70,Unknown,* NTDSAPI 75E70000 75E87FFF 00018000
03/09/2017 15:40:07,spid70,Unknown,* CLUSAPI 65890000 658BDFFF 0002e000
03/09/2017 15:40:07,spid70,Unknown,* mscoreei 74100000 74179FFF 0007a000
03/09/2017 15:40:07,spid70,Unknown,* MSCOREE 74200000 74249FFF 0004a000
03/09/2017 15:40:07,spid70,Unknown,* kerberos 75B00000 75B7DFFF 0007e000
03/09/2017 15:40:07,spid70,Unknown,* cryptdll 75F20000 75F30FFF 00011000
03/09/2017 15:40:07,spid70,Unknown,* msv1_0 75A00000 75A37FFF 00038000
03/09/2017 15:40:07,spid70,Unknown,* schannel 75830000 75876FFF 00047000
03/09/2017 15:40:07,spid70,Unknown,* credssp 75B90000 75B96FFF 00007000
03/09/2017 15:40:07,spid70,Unknown,* VERSION 75790000 75797FFF 00008000
03/09/2017 15:40:07,spid70,Unknown,* SHELL32 76770000 77280FFF 00b11000
03/09/2017 15:40:07,spid70,Unknown,* comctl32 75120000 752BDFFF 0019e000
03/09/2017 15:40:07,spid70,Unknown,* SHLWAPI 773E0000 77438FFF 00059000
03/09/2017 15:40:07,spid70,Unknown,* SensApi 74AF0000 74AF5FFF 00006000
03/09/2017 15:40:07,spid70,Unknown,* cryptnet 74630000 7464AFFF 0001b000
03/09/2017 15:40:07,spid70,Unknown,* slc 75CB0000 75CE9FFF 0003a000
03/09/2017 15:40:07,spid70,Unknown,* GPAPI 75770000 75784FFF 00015000
03/09/2017 15:40:07,spid70,Unknown,* SAMLIB 75EB0000 75EC0FFF 00011000
03/09/2017 15:40:07,spid70,Unknown,* WLDAP32 77EC0000 77F08FFF 00049000
03/09/2017 15:40:07,spid70,Unknown,* NTMARTA 75740000 75760FFF 00021000
03/09/2017 15:40:07,spid70,Unknown,* BCRYPT 75BA0000 75BE4FFF 00045000
03/09/2017 15:40:07,spid70,Unknown,* ncrypt 75BF0000 75C24FFF 00035000
03/09/2017 15:40:07,spid70,Unknown,* rsaenh 75AC0000 75AFAFFF 0003b000
03/09/2017 15:40:07,spid70,Unknown,* sqlevn70 694D0000 69743FFF 00274000
03/09/2017 15:40:07,spid70,Unknown,* cscapi 724B0000 724BAFFF 0000b000
03/09/2017 15:40:07,spid70,Unknown,* instapi110 725C0000 725CAFFF 0000b000
03/09/2017 15:40:07,spid70,Unknown,* USP10 77360000 773DCFFF 0007d000
03/09/2017 15:40:07,spid70,Unknown,* LPK 77EA0000 77EA8FFF 00009000
03/09/2017 15:40:07,spid70,Unknown,* MSCTF 77B80000 77C47FFF 000c8000
03/09/2017 15:40:07,spid70,Unknown,* IMM32 77290000 772ADFFF 0001e000
03/09/2017 15:40:07,spid70,Unknown,* imagehlp 77FA0000 77FC9FFF 0002a000
03/09/2017 15:40:07,spid70,Unknown,* MSASN1 75E90000 75EA1FFF 00012000
03/09/2017 15:40:07,spid70,Unknown,* CRYPT32 75CF0000 75DE2FFF 000f3000
03/09/2017 15:40:07,spid70,Unknown,* WINTRUST 75570000 7559CFFF 0002d000
03/09/2017 15:40:07,spid70,Unknown,* AUTHZ 76180000 76195FFF 00016000
03/09/2017 15:40:07,spid70,Unknown,* USERENV 762E0000 762FDFFF 0001e000
03/09/2017 15:40:07,spid70,Unknown,* sqllang 69750000 6B028FFF 018d9000
03/09/2017 15:40:07,spid70,Unknown,* OLEACC 74990000 749CDFFF 0003e000
03/09/2017 15:40:07,spid70,Unknown,* WINMM 749D0000 74A01FFF 00032000
03/09/2017 15:40:07,spid70,Unknown,* sqldk 6B030000 6B404FFF 003d5000
03/09/2017 15:40:07,spid70,Unknown,* sqlTsEs 6B410000 6BBD8FFF 007c9000
03/09/2017 15:40:07,spid70,Unknown,* NSI 77F10000 77F15FFF 00006000
03/09/2017 15:40:07,spid70,Unknown,* WS2_32 77F20000 77F4CFFF 0002d000
03/09/2017 15:40:07,spid70,Unknown,* Secur32 762C0000 762D3FFF 00014000
03/09/2017 15:40:07,spid70,Unknown,* OLEAUT32 77830000 778BCFFF 0008d000
03/09/2017 15:40:07,spid70,Unknown,* ole32 778C0000 77A04FFF 00145000
03/09/2017 15:40:07,spid70,Unknown,* GDI32 77F50000 77F9AFFF 0004b000
03/09/2017 15:40:07,spid70,Unknown,* USER32 77A10000 77AACFFF 0009d000
03/09/2017 15:40:07,spid70,Unknown,* sqlmin 6BBE0000 6D57BFFF 0199c000
03/09/2017 15:40:07,spid70,Unknown,* opends60 71B00000 71B06FFF 00007000
03/09/2017 15:40:07,spid70,Unknown,* pdh 6FE50000 6FE8DFFF 0003e000
03/09/2017 15:40:07,spid70,Unknown,* PSAPI 76420000 76426FFF 00007000
03/09/2017 15:40:07,spid70,Unknown,* msvcrt 772B0000 77359FFF 000aa000
03/09/2017 15:40:07,spid70,Unknown,* NETAPI32 75FC0000 76035FFF 00076000
03/09/2017 15:40:07,spid70,Unknown,* sqlos 72520000 72525FFF 00006000
03/09/2017 15:40:07,spid70,Unknown,* MSVCP100 72370000 723D8FFF 00069000
03/09/2017 15:40:07,spid70,Unknown,* MSVCR100 700E0000 7019EFFF 000bf000
03/09/2017 15:40:07,spid70,Unknown,* RPCRT4 77AB0000 77B72FFF 000c3000
03/09/2017 15:40:07,spid70,Unknown,* ADVAPI32 76510000 765D5FFF 000c6000
03/09/2017 15:40:07,spid70,Unknown,* kernel32 76430000 7650BFFF 000dc000
03/09/2017 15:40:07,spid70,Unknown,* ntdll 77D70000 77E97FFF 00128000
03/09/2017 15:40:07,spid70,Unknown,* sqlservr 004F0000 00524FFF 00035000
03/09/2017 15:40:07,spid70,Unknown,* MODULE BASE END SIZE
03/09/2017 15:40:07,spid70,Unknown,*
03/09/2017 15:40:07,spid70,Unknown,*
03/09/2017 15:40:07,spid70,Unknown,* DBCC CHECKDB(N'DBUp') WITH NO_INFOMSGS ALL_ERRORMSGS
03/09/2017 15:40:07,spid70,Unknown,* Input Buffer 140 bytes -
03/09/2017 15:40:07,spid70,Unknown,* Access Violation occurred reading address 00000000
03/09/2017 15:40:07,spid70,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
03/09/2017 15:40:07,spid70,Unknown,* Exception Address = 6BCA8C9F Module(sqlmin+000C8C9F)
03/09/2017 15:40:07,spid70,Unknown,*
03/09/2017 15:40:07,spid70,Unknown,*
03/09/2017 15:40:07,spid70,Unknown,* 03/09/17 15:40:07 spid 70
03/09/2017 15:40:07,spid70,Unknown,* BEGIN STACK DUMP:
03/09/2017 15:40:07,spid70,Unknown,*
03/09/2017 15:40:07,spid70,Unknown,* *******************************************************************************
03/09/2017 15:40:07,spid70,Unknown,SqlDumpExceptionHandler: Process 70 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
03/09/2017 15:40:07,spid70,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0049.txt
03/09/2017 15:40:07,spid70,Unknown,Using 'dbghelp.dll' version '4.0.5'

Kristen · March 10, 2017, 3:24pm

If you have space enough you could restore the new backup to a different Server / PC (to a new, temporary, database name) and CHECKDB it there. If CHECKDB is failing (on Production Server) due to some local problem on that Server then it might be clean on a different server - that will mean that your backup is "good".

I don't have high hopes of that giving you a clean copy, but if it does you can breath easy for a bit!

Your log output looks like a DUMP (following a SQL crash of some sort). Checking the SQL Error Log would be useful. You can see that in SSMS:

Management : SQL Server Logs ; Current

or by checking the raw file, but you'll need to figure out where that is stored. The current file is usually? called ERRORLOG (no extension) and historical files are called ERRORLOG.1, .2, ...

Location of ERRORLOG can be determined in a number of ways, none of them (that I know of) easy:

jeffw8713 · March 10, 2017, 7:20pm

Error state 5 is normally an access denied error - verify that the permissions on the file and folders where that database file resides haven't changed and that the service account running SQL Server has full permissions to that folder.

Integrity Checks create an internal database snapshot - that snapshot uses a hidden sparse file in the same directory where the database MDF file lives. If permissions have been changed and the service account no longer has full permissions to the folder that could cause this issue.

dawave · March 30, 2017, 11:02pm

Hi, Team

Figure I'll share with what happened later and thank again for all the great feedback. We take the DB to other machines and it still has the same checkDB error. Other DB / tweaking the DB's permission were checked too so we don't think it's the permission issue here.

We then tried checktable on each table of the problematic DB and found which table was bad. We presumed to take out that table but still can't get checkDB to work. We also tried the Free MDF repair software out there and still can't get it to work.

At the end, we verified that there wasn't much activities going on so we decided to suck it up restoring the 4 month old DB backup.

Once again. thanks for all the great feedback. XD

jason_clark · March 31, 2017, 5:29am

Have you tried this SQL Server Database Recovery Tool to recover your database try its free version which will preview all your data. This helped me a lot when I got ransomware attack and my Database was corrupt. Hope this works for you.

Kristen · March 31, 2017, 12:11pm

One thing that can, sometimes, work at that point is just to "export" each table, one-by-one. Perhaps something like this:

INSERT INTO NewDatabase.dbo.MyTable
SELECT *
FROM BadDatabase.dbo.MyTable

this will most probably read the table sequentially so a corruption in, for example, a secondary index should not cause any problem. So assuming this does manage to "export" the table completely then at least you have the data (and can recreate all secondary indexes etc.)

Might also be worth using / trying BCP to export (make sure you use Native format, as it is more robust when you Import the data compared to, say, a CSV format)

If that fails (with error) then exporting with an ORDER BY on the Clustered Index should mean that it "fails" at a given row, figure out what that is, export UP TO that row, and then try exporting from" just after" that row ... and thus recover as much of the table as possible.

When all that is done, satisfactorily, throw away the original DB and use the new one instead.

I'd suggest you make sure that you have the PAGE_VERIFY option set to CHECKSUM (after changing it you need to rebuild every index in the database for the pages to be re-written, at which point they will get the Checksum). That will, then, give you early warning if a READ is attempted on a page with an invalid checksum.

Also scheduled DBCC CHECKDB frequently (daily or weekly, if that is good enough?)

dawave · March 31, 2017, 11:04pm

We tried the free version but my team thinks that the DB was doomed.

dawave · March 31, 2017, 11:08pm

Great idea. I'll forward this to my team for sure. As I mentioned, we even deleted the table that can't pass CheckTable BUT still can't get CheckDB to pass the whole DB. So it kinda left us thinking that the whole DB is pretty messed up and yes, we are doing backup every 3 days now. XD

Kristen · April 1, 2017, 5:53pm

Seems awfully infrequent to me, unless you can easily recreate the last 3 days' worth of data?

On our OLTP databases we back up (the TLog) every minute!