April 13, 2009 at 9:21 am
Hello,
I am not sure where to post this question or all the details you might need. It has been a while since I worked with SQL, but after some recent problems I am now the one who has the responsibility.
I am working with a Win2K3 Enterprise sp2 and SQL 2005 sp2 in a Virtual environment. We had a problem a few days ago where the VM image did not successfully complete a snapshot cleanup. When we got the server back up, I had server DBs in suspect mode or showed errors during the maintenance. I restored those DBs and everything looks clean. Except, I see in the errorlog a reference to a stack dump. I really don't see where it tells me which database is the problem. The only real error I see is this one; "Error: 211, Severity: 23, State: 51".
I have been trying to search the web/this forum to find any similar issues, so I might understand where to look next. Any help that you can point me in would be greatly appreciated.
Thank you,
Monty
April 13, 2009 at 9:32 am
This error and state indicates that you've got missing information in one of the system tables in a database (i.e. metadata corruption). In the SQL Server error log (buried down in the LOG directory) you should be able to see which database is giving the problem. After that, you should be able to restore the database (sounds like you have backups).
Does that help?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 13, 2009 at 10:04 am
Paul Randal (4/13/2009)
This error and state indicates that you've got missing information in one of the system tables in a database (i.e. metadata corruption). In the SQL Server error log (buried down in the LOG directory) you should be able to see which database is giving the problem. After that, you should be able to restore the database (sounds like you have backups).Does that help?
Thanks Paul, it sort of helps. I can understand that a system table is messed up, but where in the error log should it say the DB? You dp mean the errorlog and not one of the stackdumpxxxx.log, stackdumpxxx.txt, or stackdumpxxxx.mdmp? In the errorlog, right before the stack dump output I see this line;
2009-04-11 20:30:27.08 spid56 DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
-----
after the stack dump I see this line;
2009-04-11 20:36:38.46 Backup Database backed up. Database: master, creation date(time): 2009/04/08(20:33:52), pages dumped: 371, first LSN: 1703:320:37, last LSN: 1703:344:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\master.bak'}). This is an informational message only. No user action is required.
Just not sure where to find the db name?
April 13, 2009 at 10:20 am
Should be somewhere in there - can you post the error log? (Or just email it to me - paul@sqlskills.com).
It's entirely possible (and likely) that the error came from one of the suspect databases that you've already restored. Have you run DBCC CHECKDB on all the databases since you restored them?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 13, 2009 at 10:39 am
Paul Randal (4/13/2009)
Should be somewhere in there - can you post the error log? (Or just email it to me - paul@sqlskills.com).It's entirely possible (and likely) that the error came from one of the suspect databases that you've already restored. Have you run DBCC CHECKDB on all the databases since you restored them?
Paul,
I will email you a copy of my log.
The DBs were restored last Wednesday and I have a sql maintenance job setup to run nightly. I am running the DBCCs that run when you select the DBCC in a maintenance plan, so I hope that they are right. I see all the DBs look good as far as that sql maintenance output file, but I'm still seeing the stack dump error in the log every night that the maintenance script runs.
April 13, 2009 at 10:48 am
Ah - ok you've definitely still got a corrupt database then. Do you have any output from the maintenance job(s)? One of them should be failing every night...
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 13, 2009 at 11:21 am
Paul Randal (4/13/2009)
Ah - ok you've definitely still got a corrupt database then. Do you have any output from the maintenance job(s)? One of them should be failing every night...
Hey Paul,
I sent a copy of the errorlog and the last maintenance output file to your email address.
Thanks
Monty
April 13, 2009 at 11:24 am
Got 'em.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 13, 2009 at 11:46 am
Ok - first problems is that you're running vanilla SP2, which has a bunch of bugs in maintenance plans. From the output you've sent, you're hitting the one where it repeatedly CHECKDBs master instead of the actual user database. You need to install the hotfix described in http://support.microsoft.com/kb/934459 to fix that. Then you'll *really* be running DBCC CHECKDB against the user databases.
Once you've done that and CHECKDB runs, we'll see which database has the corruption.
The stack dump looks to be coming from the cleanup part of your maintenance task, and there was also a bug there too - so the hotfix I've pointed you at above might resolve that too (it fixes the cleanup task bug).
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 13, 2009 at 12:11 pm
Thanks Paul,
As I was downloading the patch, I see that SP3 is out there. What do you think of that? Has there been any issues with that patch? Would that also resolve my problem?
April 13, 2009 at 12:17 pm
SP3 will also resolve the problem and fix a whole bunch of other bugs. As with any upgrade, make sure you test it before putting it into production - just in case some behavior changes that catches you out.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 15, 2009 at 4:39 am
Hey Paul,
Sorry for the late reply, but the hot fix worked. After I installed it and re-ran the maintenance, I found two databases that had corruption. One seemed to have a bad system table as you described, so that DB got restored. The other database has a fresh data load daily, so I did a DBCC CHECKDB ('DBNAME', repair_allow_data_loss). After that, we truncated tables and re-ran the maintenance. My error logs are now clean.
So is there anyway to add rep or give points on this board? Just wanted to say Thanks for the help!
April 15, 2009 at 6:13 am
montgomerybrothers (4/15/2009)
So is there anyway to add rep or give points on this board?
No. Points here are purely from posting and from answering the Question of the Day.
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
April 15, 2009 at 8:11 am
No problem - 'thanks' is enough.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply