August 2, 2010 at 10:58 am
In Dev, my SharePoint database that houses the indexes is in suspect. I know why, since I caused it. I believe the root cause is because I made the log file too small and limited the growth. Before the database went into suspect, there were errors about running out of log space
I just took over administration of the server, don't have a lot of SharePoint experience, and was trying to determine the optimum log file size. Learned a lesson already today.
I've been reading posts and Paul's blog, and looking for advice - http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/18/636105.aspx
It is dev, so there is no emergency. I have a good backup and restore is an easy option.
The error log is saying "SQL Server detected a logical consistency-based I/O error: incorrect checksum." It occurred during a read of page... and it references the mdf.
When I checked sys.databases, the "Log_resue_wait_desc" is "Active_transaction".
I thought I'd use this as a training exercise - rather than restoring the database. Should I try rebuild the transaction log and REPAIR_ALLOW_DATA_LOSS options, or are these things you really shouldn't do unless you have a real emergency and no good backup strategy?
Any advice is appreciated!
Thanks,
Cindy
August 2, 2010 at 2:09 pm
Restore from backup.
CheckDB with repair is a last resort for when you have no clean backups. It's not something you ever should need to do if you have a good backup strategy.
If you want to experiment, rather than playing russian roulette with a sharepoint database, search Paul Randal's blog[/url] for his sample suspect databases, load them up on a local SQL instance and play with those.
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
August 2, 2010 at 2:09 pm
Also, take a look at this article. http://www.sqlservercentral.com/articles/65804/
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
August 2, 2010 at 2:12 pm
CavyPrincess (8/2/2010)
The error log is saying "SQL Server detected a logical consistency-based I/O error: incorrect checksum." It occurred during a read of page... and it references the mdf.When I checked sys.databases, the "Log_resue_wait_desc" is "Active_transaction".
This is a simple corrupt page in the mdf. It's not affecting the log file at all, it's not influencing the log reuse. It's purely and simply a damaged portion of the data file. (unless there are other errors you haven't posted)
You should do some investigation of the IO subsystem on that server. Check for errors, outdated firmware or drivers, etc. Checksum errors are IO subsystem problems.
It was not caused by you limiting the size of the log file or by log full errors. A small log file cannot cause database corruption. SQL doesn't damage its own files. A checksum error indicates that something changed the page after it was written to disk by SQL Server.
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
August 2, 2010 at 2:27 pm
Thanks, Gail. That was the link I was trying to find and just couldn't locate it! It's in my briefcase now.
I really appreciate you steering me towards the IO subsystem. I was convinced it was something I did - just too coincidental. I'll check it out tomorrow - it is an ancient server.
I'll also go back and do the restore instead of the path I'm taking. I did the dbcc "repair_allow_data_loss" for no really good reason. Now the dbcc opentran is showing a non-existent transaction open and I can't run a dbcc checkdb.
I'll stop messing around with this, restore the database, and look for the real problem.
Thanks for the redirection.
Cindy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply