October 3, 2011 at 8:06 am
Hi All,
I got the below issue this morning and found out that the backups were not running fine.
This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB)
The thing i did is I went to the new query on that database which threw the above error and executed
DBCC CheckDB
and its a 160GB database and the query is running for the past 30 mins. This is a production db.
Do you think what i did was right? Please suggest.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 3, 2011 at 8:13 am
It would have been better to run checkdb in this manner :
DBCC CHECKDB('db name') WITH NO_INFOMSGS, ALL_ERRORMSGS
The 1st switch eliminates all the noise from the results (which makes it way easier to find errors).
The 2nd switch makes sure you see all error messages to better diagnose what to do next. The default is to return 200 errors which may not be enough.
While you wait on this I'd make sure to retrieve all my backups and start test restores on a test machine. I'd also run checkdb on those to make sure they are not corrupt.
If you don't mind losing the 35 minutes worth of waiting you just put in I'd consider killing the checkdb job and re-reunning it with the correct switches (and save the current errors you have). I personally don't really care to waste 2 hours reading 99% garbage when helping out something. same goes to you when the prod db is down.
October 3, 2011 at 8:18 am
I cancelled the DBCC CHeckDB query ...does it impact anything?
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 3, 2011 at 8:21 am
Nope, but this time you need to let it run to completion (with the correct switches).
Once it's done post the error messages and we'll see what's the best fix.
Whatever the results say, don't run checkdb with repair allow data loss.
How are those restore coming along?
October 3, 2011 at 8:38 am
Run the following, post the full and complete results.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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 3, 2011 at 9:25 am
Depending on the size of your database and the speed of your server, it may take some time for DBCC CHECKDB to finish. Hopefully you have a database backup around. The times I've seen this error it's usually a minor allocation issue which can indicate a disk issue, or I'm restoring from backup.
October 3, 2011 at 10:05 am
Thanks Guys...I have cancelled the ones I used earlier...i will be running it with switches after hours
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 3, 2011 at 10:08 am
Sapen (10/3/2011)
Thanks Guys...I have cancelled the ones I used earlier...i will be running it with switches after hours
What???
So you don't mind extending the amount of time the prod db is done??
October 3, 2011 at 10:13 am
Sapen (10/3/2011)
Thanks Guys...I have cancelled the ones I used earlier...i will be running it with switches after hours
Probably a good idea. CheckDB is not gentle on a database.
Just make sure you do run it as soon as you have an available maintenance window.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply