{"id":863,"date":"2009-05-12T20:36:00","date_gmt":"2009-05-12T20:36:00","guid":{"rendered":"\/blogs\/paul\/post\/A-sad-tale-of-mis-steps-and-corruption-(from-today).aspx"},"modified":"2017-06-20T12:13:19","modified_gmt":"2017-06-20T19:13:19","slug":"a-sad-tale-of-mis-steps-and-corruption-from-today","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sad-tale-of-mis-steps-and-corruption-from-today\/","title":{"rendered":"A sad tale of mis-steps and corruption (from today)"},"content":{"rendered":"

This is a true story, and unfolded over the last few days.\u00a0It’s deliberately written this way, I’m not trying to be patronizing – just illustrating the mistakes people can make if they don’t know what not to do.<\/p>\n

Once upon a time (well, a few days ago), there was a security person who had access to a SQL Server 2000 instance. He saw that the server had run out of disk space, but had no SQL server knowledge. He decided that\u00a0the best thing to do was to delete the log file, instead of contact the real DBA to take corrective action. This person should not have had any access to SQL Server, or the ability to delete the log. Nevertheless, the log was deleted. And then the database became suspect. Eventually, with no other option, someone\u00a0decided to create a new transaction log file using DBCC REBUILD_LOG<\/em>. This was the right thing to do. The database wasn’t being backed up so restore wasn’t an option. Some of the details of who did what are unclear, as it so often the case. Needless to say, the real DBA was kept in the dark.<\/p>\n

Unfortunately,\u00a0whoever rebuilt the log\u00a0didn’t run DBCC CHECKDB<\/em> afterwards to find out what corruption had been caused in the database by\u00a0having the transaction log unceremoniously ripped out from under the database’s feet. Rebuilding a transaction log is one of the worst things that can happen to a database, as all active transactions are thrown away, with no chance of rolling back. This leaves the database in a transactionally inconsistent, and potentially structurally inconsistent state (see Corruption: Last resorts that people try first…<\/a><\/em>\u00a0filed under my Bad Advice<\/a> category, and Q4 from TechNet Magazine: February 2009 SQL Q&A column<\/a>: Is it ever safe to rebuild a transaction log?<\/em>).<\/p>\n

Instead they carried on running. The real DBA thought everything was cool and even upgraded the instance from SQL 2000 to SQL 2005. To be honest, after seeing the 19000+ corruptions they had in the database, I’m surprised that the database upgraded successfully. But it did. Then he tried to rebuild some indexes, which failed with corruption errors. Which brought us to this morning, when DBCC CHECKDB was run. It reported a lot of corruption, but running from SSMS only showed the first 1000 errors (which was the inspiration for one of my previous posts today How to get all the corruption messages from CHECKDB<\/a><\/em>. So they started to run REPAIR_ALLOW_DATA_LOSS<\/em>, which will cause data loss – we chose the name of the repair option well. I got involved at this point, and saw from the list of errors that massive data loss would be the result.<\/p>\n

Once they had re-run DBCC CHECKDB<\/em> through the command line (luckily only 20 minutes) I could see all the errors. And I must say I was astounded. It was the *worst* case of multiply-allocated extents (see Inside the Storage Engine: Anatomy of an extent<\/a><\/em>) that I’ve ever seen. Not only were the extents allocated by multiple IAM pages (see Inside the Storage Engine: IAM pages, IAM chains, and allocation units<\/a><\/em>) – in other words, two objects thought they had the same pages in the database allocated, but the two objects had actually each proceeded to overwrite the other’s updates in the pages – getting the two clustered indexes hopelessly interlinked. Lots of errors (1,000s)\u00a0like:<\/p>\n

\r\nMsg 8904, Level 16, State 1, Server NAMESCHANGEDTOPROTECTTHEINNOCENT, Line 1\r\nExtent (1:9528) in database ID 5 is allocated by more than one allocation object.\r\n...\r\nMsg 8978, Level 16, State 1, Server NAMESCHANGEDTOPROTECTTHEINNOCENT, Line 1\r\nTable error: Object ID 1445580188, index ID 1, partition ID 376212519911424, alloc unit ID 94737543200768 (type In-row data). Page (1:52696) is missing a reference from previous page (1:427112). Possible chain linkage problem.\r\n...\r\nMsg 8977, Level 16, State 1, Server NAMESCHANGEDTOPROTECTTHEINNOCENT, Line 1\r\nTable error: Object ID 1445580188, index ID 1, partition ID 376212519911424, alloc unit ID 94737543200768 (type In-row data). Parent node for page (1:143210) was not encountered.\r\n...\r\nMsg 2533, Level 16, State 1, Server NAMESCHANGEDTOPROTECTTHEINNOCENT, Line 1\r\nTable error: page (1:405139) allocated to object ID 1445580188, index ID 1, partition ID 376212519911424, alloc unit ID 94737543200768 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.\r\n...\r\nCHECKDB found 1653 allocation errors and 17646 consistency errors in database 'NAMESCHANGEDTOPROTECTTHEINNOCENT'.\r\n<\/pre>\n
The repair for all of this? Delete it all and fix up the links. MASSIVE data loss.<\/p>\n
Lessons to learn from this:<\/p>\n