{"id":1046,"date":"2014-01-16T13:46:37","date_gmt":"2014-01-16T20:46:37","guid":{"rendered":"http:\/\/jim-zimmerman.com\/?p=1046"},"modified":"2014-01-16T13:46:37","modified_gmt":"2014-01-16T20:46:37","slug":"repair-microsoft-sql-databasetable-lessons","status":"publish","type":"post","link":"https:\/\/jim-zimmerman.com\/?p=1046","title":{"rendered":"Repair Microsoft SQL database\/table lessons."},"content":{"rendered":"<p>I was getting the following kinds of errors after an errant update to a Microsoft SQL Server (2005 in this case) while running a &#8220;DBCC CHECKDB&#8221;:<\/p>\n<blockquote><p>DBCC results for &#8216;Transactions&#8217;.<br \/>\nMsg 8928, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594042843136 (type In-row data): Page (1:1169) could not be processed.  See other errors for details.<br \/>\nMsg 8939, Level 16, State 98, Line 1<br \/>\nTable error: Object ID 37575172, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594042843136 (type In-row data), page (1:1169). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.<br \/>\nMsg 8928, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594042843136 (type In-row data): Page (1:1309) could not be processed.  See other errors for details.<br \/>\nMsg 8939, Level 16, State 98, Line 1<br \/>\nTable error: Object ID 37575172, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594042843136 (type In-row data), page (1:1309). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1337), slot 12 should be pointed to by forwarding row page (1:1169), slot 20. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1337), slot 38 should be pointed to by forwarding row page (1:1169), slot 32. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1337), slot 41 should be pointed to by forwarding row page (1:1169), slot 41. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1338), slot 5 should be pointed to by forwarding row page (1:1169), slot 45. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1338), slot 7 should be pointed to by forwarding row page (1:1309), slot 43. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1338), slot 23 should be pointed to by forwarding row page (1:1169), slot 56. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1338), slot 24 should be pointed to by forwarding row page (1:1169), slot 59. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1338), slot 29 should be pointed to by forwarding row page (1:1169), slot 62. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1338), slot 33 should be pointed to by forwarding row page (1:1309), slot 66. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1339), slot 25 should be pointed to by forwarding row page (1:1309), slot 38. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1340), slot 17 should be pointed to by forwarding row page (1:1309), slot 51. Did not encounter forwarding row. Possible allocation error.<br \/>\nMsg 8994, Level 16, State 1, Line 1<br \/>\nObject ID 37575172, forwarded row page (1:1340), slot 20 should be pointed to by forwarding row page (1:1309), slot 22. Did not encounter forwarding row. Possible allocation error.<br \/>\nThere are 49003 rows in 821 pages for object &#8220;Transactions&#8221;.<br \/>\nCHECKDB found 0 allocation errors and 16 consistency errors in table &#8216;Transactions&#8217; (object ID 37575172).<\/p><\/blockquote>\n<p>Here is what I did\/tried and what eventually worked:<\/p>\n<p>Put the database in single user mode:<\/p>\n<blockquote><p>ALTER DATABASE databasename SET SINGLE_USER<\/p><\/blockquote>\n<p>Examined and saved some of the contents of a corrupt page:<\/p>\n<blockquote><p>DBCC PAGE (&#8216;tablename&#8217;, 1, pagenumber, 3) WITH TABLERESULTS<\/p><\/blockquote>\n<p>Tried to rebuild the indexes in the corrupt table:<\/p>\n<blockquote><p>DBCC DBREINDEX (&#8220;tablename&#8221;, &#8221; &#8220;, 70)<\/p><\/blockquote>\n<p>Tried to repair the database without data loss:<\/p>\n<blockquote><p>DBCC CHECKDB(&#8216;databasename&#8217;, REPAIR_REBUILD)<\/p><\/blockquote>\n<p>Eventually gave in and repaired the database with possible data loss:<\/p>\n<blockquote><p>DBCC CHECKDB(&#8216;databasename&#8217;, REPAIR_ALLOW_DATA_LOSS);<\/p><\/blockquote>\n<p>Enabled multi-user mode:<\/p>\n<blockquote><p>ALTER DATABASE databasename SET MULTI_USER<\/p><\/blockquote>\n<p>Fortunately, it doesn&#8217;t look like much data was lost, since there was no backup.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I was getting the following kinds of errors after an errant update to a Microsoft SQL Server (2005 in this case) while running a &#8220;DBCC CHECKDB&#8221;: DBCC results for &#8216;Transactions&#8217;. Msg 8928, Level 16, State 1, Line 1 Object ID 37575172, index ID 0, partition ID 72057594038845440, alloc unit ID 72057594042843136 (type In-row data): Page [&#038;hellip<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[355,356,218],"class_list":["post-1046","post","type-post","status-publish","format-standard","hentry","category-documentation","tag-dbcc","tag-index","tag-mssql"],"share_on_mastodon":{"url":"","error":""},"_links":{"self":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1046"}],"version-history":[{"count":2,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1046\/revisions"}],"predecessor-version":[{"id":1048,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/1046\/revisions\/1048"}],"wp:attachment":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}