March 13, 2008 at 7:57 am
I had a database marked as suspect
Heres the logs on why it became suspect:
2008-03-13 02:53:09.42 spid16Starting up database 'mydatabase'.
2008-03-13 02:53:16.87 spid162258 transactions rolled forward in database 'mydatabase' (7).
2008-03-13 02:53:17.21 spid161 transactions rolled back in database 'mydatabase' (7).
2008-03-13 02:53:17.25 spid16Recovery is checkpointing database 'mydatabase' (7)
2008-03-13 02:53:25.23 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:25.23 spid53ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre
2008-03-13 02:53:26.28 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:26.28 spid53ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre
2008-03-13 02:53:26.28 spid53A possible database consistency problem has been detected on database 'mydatabase'
2008-03-13 02:53:26.28 spid53Error: 7987, Severity: 22, State: 3
2008-03-13 02:53:26.28 spid53Stack Signature for the dump is 0x6DAD4D8E
2008-03-13 02:53:27.85 spid53Stack Signature for the dump is 0x23D39529
2008-03-13 02:53:27.87 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:27.87 spid53ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to cre
2008-03-13 02:53:27.87 spid53A possible database consistency problem has been detected on database 'mydatabase'
2008-03-13 02:53:27.87 spid53Error: 7987, Severity: 22, State: 3
2008-03-13 02:53:29.53 spid53Stack Signature for the dump is 0x6CE596A3
2008-03-13 02:53:29.75 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:31.35 spid53SQL Server Assertion: File: , line=3176 ...
2008-03-13 02:53:31.35 spid53Stack Signature for the dump is 0xD985F6DE
2008-03-13 02:53:31.37 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:31.37 spid53ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre
2008-03-13 02:53:31.37 spid53Error while undoing logged operation in database 'mydatabase'. Error at log record
2008-03-13 02:53:31.37 spid53Error: 3314, Severity: 21, State: 4
2008-03-13 02:53:31.37 spid53Error: 3624, Severity: 20, State: 1.
2008-03-13 02:53:32.98 spid53Stack Signature for the dump is 0xCA4BC80C
2008-03-13 02:53:33.01 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:33.01 spid53ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to cre
2008-03-13 02:53:33.01 spid53A possible database consistency problem has been detected on database 'mydatabase'
2008-03-13 02:53:33.01 spid53Error: 7987, Severity: 22, State: 3
2008-03-13 02:53:34.73 spid53Stack Signature for the dump is 0x598648EE
2008-03-13 02:53:34.93 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:36.60 spid53SQL Server Assertion: File: , line=3176 ...
2008-03-13 02:53:36.60 spid53Stack Signature for the dump is 0x34DE50F1
2008-03-13 02:53:36.62 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:38.34 spid53An error occurred while processing the log for database 'mydatabase'..
2008-03-13 02:53:38.34 spid53Error: 9004, Severity: 23, State: 7
2008-03-13 02:53:38.34 spid53SQL Server Assertion: File: , line=3399 ...
2008-03-13 02:53:38.34 spid53Stack Signature for the dump is 0x43763594
2008-03-13 02:53:38.35 spid53Error while undoing logged operation in database 'mydatabase'. Error at log record
2008-03-13 02:53:38.35 spid53Error: 3314, Severity: 21, State: 4
2008-03-13 02:53:38.43 spid53The log for database 'mydatabase' is not available..
2008-03-13 02:53:38.43 spid53Error: 9001, Severity: 21, State: 1
2008-03-13 02:53:38.71 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:40.21 spid53Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:40.21 spid53SQL Server Assertion: File: , line=3176 ...
2008-03-13 02:53:40.21 spid53Stack Signature for the dump is 0xBEE481FB
2008-03-13 02:53:41.37 spid53SQL Server Assertion: File: , line=3399 ...
2008-03-13 02:53:41.37 spid53Stack Signature for the dump is 0x0530B349
2008-03-13 02:53:41.39 spid53Error while undoing logged operation in database 'mydatabase'. Error at log record
2008-03-13 02:53:41.39 spid53Error: 3314, Severity: 21, State: 5
2008-03-13 02:53:41.39 spid53An error occurred while processing the log for database 'mydatabase'..
2008-03-13 02:53:41.39 spid53Error: 9004, Severity: 23, State: 7
2008-03-13 02:53:42.53 spid16Starting up database 'mydatabase'.
2008-03-13 02:53:43.96 spid163 transactions rolled forward in database 'mydatabase' (7).
2008-03-13 02:53:44.06 spid16ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to cre
2008-03-13 02:53:44.07 spid16Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:45.76 spid16Stack Signature for the dump is 0xD03D644B
2008-03-13 02:53:45.82 spid16Using 'dbghelp.dll' version '4.0.5'...
2008-03-13 02:53:45.82 spid16ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to cre
2008-03-13 02:53:45.82 spid16A possible database consistency problem has been detected on database 'mydatabase'
2008-03-13 02:53:45.82 spid16Error: 7987, Severity: 22, State: 3
2008-03-13 02:53:47.53 spid16Stack Signature for the dump is 0xF901BC45
2008-03-13 02:53:47.54 spid16Error while undoing logged operation in database 'mydatabase'. Error at log record
2008-03-13 02:53:47.54 spid16Error: 3314, Severity: 21, State: 3
2008-03-13 02:53:47.79 spid16An error occurred while processing the log for database 'mydatabase'..
2008-03-13 02:53:47.79 spid16Error: 9004, Severity: 23, State: 7
2008-03-13 02:53:47.81 spid16Database 'mydatabase' (database ID 7) could not recover. Contact Technical Support
2008-03-13 02:53:47.81 spid16Error: 3414, Severity: 21, State: 1
2008-03-13 02:55:29.10 spid3SQL Server is terminating due to 'stop' request from Service Control Manager.
I winded up detaching the database deleting the LDF file and I try to re-attach with MDF only but I get a "You must run DBCC CHECKDB" error.
Please help out entire site is down becaue of this.
Thanks
Pete
March 13, 2008 at 8:06 am
Hey Pete,
You can try the process described in this URL
http://msdn2.microsoft.com/en-us/library/ms188424.aspx
After stopping and restarting the SQL Service If the db is still marked as suspect a restore maybe in order?
Gethyn Elliswww.gethynellis.com
March 13, 2008 at 9:42 am
I forgot to mention this is a SQL2000 database
March 13, 2008 at 9:48 am
I tried sp_resetstatus 'mydatabase' and tells me the database does not exist.
This database was detached after it became suspect. Now it does not even show up in enterprisse manager.
I also tried re attaching MDF with no luck - it tells me cannot attach I need to run DBCC CHECKDB
but DBCC CHECKDB says the database does not exist
Need help ASAP!
Thanks
March 13, 2008 at 10:05 am
Do you have a backup, restoring maybe may be your only alternative.
Gethyn Elliswww.gethynellis.com
March 13, 2008 at 10:10 am
Why did you detach the database and delete the transaction log? That's not going to fix the corruption. See this post on why trashing your log is not good http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx
sp_resetstatus is not going to work for a detached database.
You need to restore from your backups but I'm guessing you don't have any? In that case, you need to create a dummy database of the same size and layout as the one you just trashed, shutdown and swap in your remaining mdf file, then restart. Then the database will come up as suspect.
oh - just saw your post that this is 2000 (this is a 2005 forum btw) - that makes it worse as it cuts out some of the options that 2005 added.
ok - so you'll need to put the database into emergency mode - using the sp_resetstatus isn't going to do anything because you trashed your log file - so the database can't run recovery (i.e. its transactionally inconsistent and possibly structurally corrupt, depending on what transaction were active in the log).
To put the DB in emergency mode in 2000, you need to hack the sysdatabases table so the status field has the 'bypass recovery' bit set. You can do this by reconfiguring the server to allow updates. Do the following:
sp_configure "allow updates",1
reconfigure with override
go
update sysdatabases set status= 32768 where name = "mydb"
go
sp_configure "allow updates",0
reconfigure with override
go
Then your database is in emergency mode and you can export all the data to a new database or rebuild the transaction log and run a full CHECKDB with REPAIR_ALLOW_DATA_LOSS. However - bear in mind that you will have lost data.
If you need help with any of this I suggest you call Product Support - waiting for forum responses isn't the most ideal way of getting help if your business is down.
Hope this helps.
(I really must do a blog post on this stuff...)
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
March 13, 2008 at 10:39 am
Actually I did not DELETE the LDF i saved it in another folder so I still have it. But when I try to attach it it gives me error need to run DBCC CHECKDB but it tells me it cannot find the database
March 13, 2008 at 10:48 am
Ah - in your original post you specifically said you deleted it.
Do you have a backup? Restoring is the way to go if you have one.
ok - so the problem is that recovery hit a corrupt page in your database while trying to rollback a transaction - so the database is transactionally inconsistent. You're not going to be able to run CHECKDB with repair until recovery has completed on the database which means you're going to have to rebuild your transaction log.
I really recommend you call Product Support to help you through this if your business is down - the forums aren't the right medium for walking someone through disaster recovery.
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
March 13, 2008 at 11:31 am
Thanks - 1 more question:
I have a full back up from Feb 15th and Differential backups up untill last night.
How can I restore? My database is no longer attached and therefor does not show up in entrprise manager.
And I cannot re-attach it
March 13, 2008 at 11:39 am
Re-attach the database with the log file and it will be suspect. Backup the tail of the log (by doing a regular log backup and adding WITH NO_TRUNCATE). Then restore your full backup, the last differential you took, and all the log backups sinces the differential, up to and including the log-tail backup. Make sure you restore all the backups using WITH NORECOVERY. When you're done with all the restores, then do a RESTORE DATABASE dbname WITH RECOVERY and that will force recovery to run.
Hopefully that should work...
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
March 13, 2008 at 12:24 pm
Thanks
The only problem is it will not let me reattach.
When I try to reattach it tells me error run CHECKDB
Then it wont let me run checkdb because the database is not there.
So I am stuck. If I could only reattach it then I could do what you said.
March 13, 2008 at 12:29 pm
ok - so follow the advice from the first response I gave:
In that case, you need to create a dummy database of the same size and layout as the one you just trashed, shutdown and swap in your remaining mdf file, then restart. Then the database will come up as suspect.
Let me know how you get on.
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
March 13, 2008 at 2:42 pm
It Worked!
You are a genius!
Heres what I did:
Folowed the steps you said to re attach it.
Restored backup from feb 15th and transactions since then.
Everything is up no loss
Thank you very much!
March 13, 2008 at 3:03 pm
Very cool. Beers are on you 🙂
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
March 13, 2008 at 4:21 pm
No - because he would have lost the tail of the log with all the transactions since the last log backup. To get up to the minute recovery, the first thing you should *always* think about is how can I backup the tail of the log.
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 25 total)
You must be logged in to reply to this topic. Login to reply