Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Transaction Log Full in Simple Recovery mode Expand / Collapse
Author
Message
Posted Wednesday, February 9, 2011 12:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
clare.xia (2/9/2011)
Msg 824, Level 24, State 2, Line 1
SQL 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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1061454
Posted Wednesday, February 9, 2011 12:09 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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!
Post #1061459
Posted Wednesday, February 9, 2011 12:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
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...
Post #1061469
Posted Wednesday, February 9, 2011 12:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #1061470
Posted Wednesday, February 9, 2011 12:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
IO subsystem problems. Bad sector is a good potential candidate for the cause.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1061478
Posted Wednesday, February 9, 2011 12:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:43 AM
Points: 56, Visits: 249
Wow.. I can't believe I got all the attentions from SQL Gurus! This is a great learning experience for me!

Here are the references I have been using. Please let me know if you think some other articles will be helpful.

Fix Suspect DB

http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx


Recreate MSDB

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/06/619304.aspx


I appreciate all your helps! SQL is fun!
Post #1061482
Posted Wednesday, February 9, 2011 2:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
clare.xia (2/9/2011)
Wow.. I can't believe I got all the attentions from SQL Gurus! This is a great learning experience for me!

Here are the references I have been using. Please let me know if you think some other articles will be helpful.

Fix Suspect DB

http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx


Recreate MSDB

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/06/619304.aspx


I appreciate all your helps! SQL is fun!


Wow talk about great spirit! Top of the class!!!
Post #1061585
Posted Tuesday, October 4, 2011 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 23, 2011 7:43 AM
Points: 5, Visits: 26
Run CHKDSK (no repair function) on the Disk that contains the corrupt file....

If you receive these errors:

/* WARNING! F parameter not specified.
Running CHKDSK in read-only mode.

CHKDSK is verifying files (stage 1 of 3)...
6176 file records processed.
File verification completed.
133 large file records processed.
0 bad file records processed.
0 EA records processed.
0 reparse records processed.
CHKDSK is verifying indexes (stage 2 of 3)...
13853 index entries processed.
Index verification completed.

Errors found. CHKDSK cannot continue in read-only mode. */

Then you have an NTFS problem (these errors can be found on a local or SAN storage). You can try and fix the error by shutting down SQL and running chkdsk with automatic fix or allocate new Disks and restore your databases.

Hope this helps,
David
Post #1185343
Posted Wednesday, October 5, 2011 3:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 8:39 AM
Points: 23, Visits: 39
Just a thought for everyone and more of a question than a suggestion but in this situation would not be worth shrinking the log file with DBCC SHRINKFILE?

I have no idea if this will work when the disk is corrupt but if it does re-arrange the smaller file so it doesnt occupy the corrput sector the db may come back and you may be able to back it up, fix the disk prob as others have outlined and restore it.

As there is only simple recovery, the contents of the log file are of little practical value anyway arent they?

Post #1185724
Posted Wednesday, October 5, 2011 7:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
99zardoz (10/5/2011)
Just a thought for everyone and more of a question than a suggestion but in this situation would not be worth shrinking the log file with DBCC SHRINKFILE?


Shrinkfile would have failed, the log header was corrupt. No amount of shrinking would help here, the problem was not that the log was too large, it was that the log was corrupt.

As there is only simple recovery, the contents of the log file are of little practical value anyway arent they?


Do you like your database consistent and durable or corrupt with missing data? If the latter, sure, they're of little practical use. The point of the log is to ensure durability of transactions and consistency of data no matter what. The backing up of the log for point-in-time recovery is a secondary usage.

p.s. this issue is 8 months old and very likely resolved one way or another by now.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1185855
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse