October 8, 2009 at 11:48 am
My db got corrupted what do I need to do, is it going cause performence, I have web side running.
October 8, 2009 at 11:55 am
First, read this. http://www.sqlservercentral.com/articles/65804/
Second, run the following code, post the full results here.
DBCC CHECKDB ({Database Name}) WITH NO_INFOMSGS, ALL_ERRORMSGS
Do you have a clean backup? One that doesn't have the corruption in it?
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 8, 2009 at 11:57 am
yes, I have a clean backup. My last transaction job ran at 12 and corruption happend at 1256
October 8, 2009 at 12:11 pm
Krasavita (10/8/2009)
yes, I have a clean backup. My last transaction job ran at 12 and corruption happend at 1256
Did the corruption happen at 12:56 or did you detect it at 12:56? Big difference.
What's the error message that you're getting?
What does the checkDB command I posted return?
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 8, 2009 at 12:27 pm
corruption happend at 1256 with messages:... page header is all zeroes
(0:0)/(1:11)0/1,
pageno is/should be:objid is/should be:
Getpage: bstat=0x9, sstat=0xa00, cache
lot of them and then
Getpage: bstat=0x9, sstat=0xa00, cache
SqlDumpExceptionHandler: Process 79 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Error: 0, Severity: 19, State: 0
Attempt to fetch logical page (1:38) in database 'dbdata' belongs to object '0', not to object 'sysusers'..
SqlDumpExceptionHandler: Process 2212 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Using 'dbghelp.dll' version '4.0.5'
Stack Signature for the dump is 0x022C071A
process_commands: Process 2212 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
October 8, 2009 at 12:31 pm
Before I run DBCC CHECKDB ({Database Name}) WITH NO_INFOMSGS, ALL_ERRORMSGS. Is this going to cause my webside to go down?
October 8, 2009 at 12:33 pm
Krasavita (10/8/2009)
corruption happend at 1256 with messages:... page header is all zeroesError: 0, Severity: 19, State: 0
Attempt to fetch logical page (1:38) in database 'dbdata' belongs to object '0', not to object 'sysusers'..
That means you detected it at 12:56. It could have occurred at any point before that. This kind of corruption is typically an IO subsystem problem and a faulty IO subsystem doesn't write into the error log when it messes up.
Now, what does the CheckDB output look like?
Also, take your latest full backup, restore it on a test server and run the same CheckDB statement against that.
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 8, 2009 at 12:34 pm
Krasavita (10/8/2009)
Before I run DBCC CHECKDB ({Database Name}) WITH NO_INFOMSGS, ALL_ERRORMSGS. Is this going to cause my webside to go down?
No, but it may slow things down.
Make sure you replace {Database Name} with the actual name of the database.
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 8, 2009 at 1:52 pm
Should run under master
October 8, 2009 at 1:59 pm
Krasavita (10/8/2009)
Should run under master
Anywhere. You're specifying the name of the database in the command. It can run from any database, doesn't matter.
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 8, 2009 at 2:08 pm
I just ran in and it was succussful
October 8, 2009 at 2:15 pm
Thank you, any idea what to do next
October 8, 2009 at 2:17 pm
A checkDB on the database dbdata returned no error messages?
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 8, 2009 at 2:19 pm
Command(s) completed successfully.
October 8, 2009 at 2:48 pm
Go into that database and run DBCC CHECKTABLE (sysusers) and post the results.
Also run the following
DBCC IND (yourdbname, sysusers, -1)
and post the results. That will list all the pages allocated to the sysusers table.
Were you adding users or logins at the time the problem occured?
Can you also run:
DBCC TRACEON (3604)
DBCC PAGE (yourdb, 1, 38, 2)
And post the results (at least for the page header portion). It's entirely possible that your I/O subsystem has stale read problems, or isn't reading the right portion of one of the drives when SQL Server asks for offset (38 x 8192) in the data file.
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
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply