February 9, 2011 at 10:52 am
Have a backup handy?
February 9, 2011 at 10:53 am
No. I know!
February 9, 2011 at 10:57 am
Any chance you have another system db corrupt that could cause this error?... just a hunch and maybe this could lead somewhere.
February 9, 2011 at 10:58 am
clare.xia (2/9/2011)
No. I know!
old backup of the Vm machine (assuming virtual env.)?
February 9, 2011 at 11:12 am
It isn't clear to me in the earlier posts if you have attempted to issue an explicit CHECKPOINT command?
I might get crucified for suggesting this because I have no experience with this specific situation, but might you be able to change the PAGE_VERIFY option of the database from CHECKSUM to TORN_PAGE_DETECTION or NONE to get a backup? I'm not suggesting running that way, but it might get you past the error to get a backup you could possibly restore. One of the other people monitoring this thread may have better knowledge of what this will really do or if it will help at all.
I'm sure Gail will chime in and let me know how bad an idea this is. 😀
You might want to check out this article as well, http://support.microsoft.com/kb/2015756
Also take a look at the whitepaper here, http://technet.microsoft.com/en-us/library/cc917726.aspx, page 23 starts talking about the PAGE_VERIFY options.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 9, 2011 at 11:13 am
The transaction log is corrupt, specifically the log header. I'm guessing that's the root cause of this.
Export all the data from MSDB and then either copy over from another server or (preferably) recreate MSDB (there are scripts and walkthroughs on the MS site). Once you've done that, you can reimport data.
Export the backup related tables, that's your backup history.
Script out all jobs so that you can recreate them. Same with agent alerts.
If there are any SSIS or DTS packages, save them to the filesystem
That should be the most important stuff, but check the other tables and ask if you're not sure.
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
February 9, 2011 at 11:15 am
Jack Corbett (2/9/2011)
I might get crucified for suggesting this because I have no experience with this specific situation, but might you be able to change the PAGE_VERIFY option of the database from CHECKSUM to TORN_PAGE_DETECTION or NONE to get a backup?
Gut feel, it won't work because that's a logged operation and due to the corruption in the log header (at least I assume that's the cause), SQL thinks that the log is full.
Can try, no harm.
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
February 9, 2011 at 11:46 am
Thank you so much for the advice! Could you please let me know how to find out the header of the log is bad?
February 9, 2011 at 11:49 am
clare.xia (2/9/2011)
Thank you so much for the advice! Could you please let me know how to find out the header of the log is bad?
Just send a mail to santa clause... he knows that kind of stuff. :hehe:
Seriously no idea, but I'm sure Gail will know.
February 9, 2011 at 11:55 am
You are right. I can't change that option. It complain the same thing.. log is full!
February 9, 2011 at 12:01 pm
clare.xia (2/9/2011)
Msg 824, Level 24, State 2, Line 1SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x6f9014c7; actual: 0x6f903ecb). It occurred during a read of page (2:0) in database ID 4 at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
File 2 is the log file, confirmed by the name. Page 0 is at the beginning of the file, confirmed by the offset. At the beginning of the log is the log header. Hence you have a checksum error (yes, logs do have checksums) in the log header. Not repairable.
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
February 9, 2011 at 12:09 pm
Thanks a again! One more qustion, Is this logged in msdb..suspect_pages?
select * from msdb..suspect_pages
database_id file_id page_id event_type error_count last_update_date
----------- ----------- -------------------- ----------- ----------- -----------------------
4 2 0 2 2 2010-06-24 04:53:41.707
(1 row(s) affected)
Looks like the error happened long time ago!
February 9, 2011 at 12:17 pm
Sorry.. another question. What kind of cause you can think of? I heard the sector of the hard drive on that server may have problem...
February 9, 2011 at 12:17 pm
You're not going to be able to repair this - that log file is now toast.
Here's what I would try (follow these instructions at your own risk):
1) shutdown the server
2) copy off the msdb files
3) delete the msdb log file
4) start the server
5) emergency mode repair of msdb (you may need T3608 for this - don't remember)
6) make sure all your jobs and SSIS packages are still there
OR
1) script out all information from msdb
2) create a new msdb
3) reinsert all information into the new msdb
If either of these are beyond the scope of your comfort/expertise, you need to get someone else to help you.
No matter which you choose, you need to analyze the IO subsystem to find out why the log file became corrupt.
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
February 9, 2011 at 12:24 pm
IO subsystem problems. Bad sector is a good potential candidate for the cause.
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 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply