Detecting Database Corruption – Video

You don’t have to be Sherlock Holmes to detect Server Corruption. Steve Stedman and Derrick Bovenkamp share valuable information on how to spot server corruption before the damage is irreversible.

And we are always here for guidance or complete corruption repair if you need us!

Transcription:

Derrick Bovenkamp 0:10
How do we find corruption? Man, I wish it was as easy as taking out the magnifying glass

Steve Stedman 0:15
Yeah, if we had this magic magnifying glass that would let us look at the data bytes like that, that’d be cool. But we don’t unfortunately. So the tools we have, the first one is check dB. And this is really the, I guess the number one standard for finding corruption. And with check dB, run it frequently have alerting on it. And we’ll go in a little bit more detail on that later. But when you run check dB, the nice thing is for those who are not colorblind is that everything that check DB puts in the output window that’s red is usually a problem. And everything that’s black is usually Okay, or a summary of the problem. So the quick thing is you run check DB on whichever database you’re looking into. And you then scan through the results and look for anything red, and anything red is really bad and has to be dealt with at that point.

Derrick Bovenkamp 1:00
Yeah, and the next thing that you could do is, especially if your databases too big to get check DB back in a reasonable time, if you suspect corruption is you could try just a check table on you know, in this case, hey, we think there’s something up with revenue, let’s do a check table on revenue.

Steve Stedman 1:19
Yep. And that’s something that if you can script or automate these, it’ll make things a whole lot easier. Now, some people say, Well, can I just run check table on all my tables in the database and not run a check DB? Yeah, you could do that. But you’re gonna end up missing things. And there’s some other checks that are part of dbcc, like check constraints, check, catalog, check, Alec, check file, group check identity, and checking system tables as well, because when you run check table, you’re usually not checking the hidden system tables you don’t even see as part of the database. And when those become corrupt, that’s usually one of the one of I guess we could call it one of the more difficult ones that we’ve had to repair. But you can run all these other things, check specific areas.

Derrick Bovenkamp 1:59
And you know, you can also look at suspect pages. And, you know, see if there’s any side pages there. We have seen this before, a lot of the times you’ll actually see errors in the error log, the SQL server error log, but you know, the suspect pages sometimes sometimes you’ll see those if they may have used to have corruption, and it was repaired and the suspect pages weren’t cleared out of that. Yep, let’s make pages stable.

Steve Stedman 2:27
And if you happen to download any of the database corruption, challenge corrupt databases, and then do the Restore, usually doing the Restore injects data into the suspect pages, that there’s something corrupt. So just another table to check regularly. Also, just running a query may show corruption, select star from revenue, and you’re expecting piles and piles of money from your company’s revenue from your accounting database. But instead, you get a logical consistency based IO err, well, that’s no good. Anytime you anytime you get read output like this, this is something that really needs some attention.

Derrick Bovenkamp 2:57
Yeah. And you know, the other thing that, you know, before I started working with Steve, like, I wouldn’t have guessed as possible. But I, as I say, you know, put your smell test next to any output you get. Because if you expect revenue to have, let’s say, 1000 rows in it, and you’re selecting on the revenue, and it just keeps selecting and selecting and selecting and selecting, and you’re at 50,000 rows, something might be

Steve Stedman 3:22
up. Yep. And there’s actually an example in the database corruption challenge that shows where the linkage between pages in the database gets mixed up and put you in an endless loop and select star from table, the table will run until you run out of memory, because it just keeps repeating the same rows over and over and over again. And that’s a real bad spot to be in. Also, if you’ve restarted your server, remember earlier, we said, Don’t Don’t reboot your SQL Server, it may make things worse, well, if you if you did do that, and you’ve got a corrupt database, it may not come back online. And you may be either in a recovery pending state, or in a suspect state. Now, recovery pending is okay for a little bit. And what happens is, after you’ve done an unclean shutdown to the SQL Server, it goes through what’s called crash recovery mode, where it’s rebuilding all those open transactions that were running when the date when the SQL Server was shut down. And depending on the speed of your server that may take a few minutes to a few hours. But when that’s going on, you usually see things in the error log to show that the database is is moving, it’s attempting to be recovered. If it’s not moving, or it’s been in recovery pending mode for days, it’s probably never gonna come back without some type of intervention.

Derrick Bovenkamp 4:28
Yeah, and I would also add, sometimes you’ll see that recovery pending with a really, really slow storage. I can’t it just that screenshot almost sent shivers down my spine because I’ve worked on a few SQL Servers where the storage was so slow, and the database was so big that every time you restart SQL, it would take five or 10 minutes for it to come back up and wondering if there’s actually a problem or if it’s going to come online.

---