October 6, 2008 at 4:18 am
Hi all,
after implementing our archiving strategy i want to run a checkDB because i'll be clearing / reorganising / shrinking about 80GB from the DB and would rather be safe than sorry.
This database us currently being setup for transactional replication.....
If a repair is required, what is the best strategy for taking down (and bringing back up) replication to put the DB in single user mode?
any advice much appreciated
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
October 6, 2008 at 6:47 am
The recommended way to fix corruption is to restore a clean backup. Repair should be a last resort when there is no good backup available.
p.s. Rebuild your indexes after the shrink, not before.
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 7, 2008 at 3:46 am
but, running a repair / rebuild will take roughly 10mins as opposed to an hour to restore from previous nights backup and all the transaction logs.....and what if the previous nights backup is also corrupt? I've had this issue before where one of the pages in the database had a corrupt line which didn't come to light until a delete was ran against that table - a full select * on that table brought back all records fine!!!! Running a checkDB was what SQL Server recommended so i did and it fixed it so i'd like to run it as a precaution.
However, if i try and run it on a database which is begin replicated it won't let me put it into Single User Mode. If i am to disable the replication it will delete the subscriptions. Is there anyway just to say, "disable replication for now" but do not delete any subscriptions etc etc???
PS: The reorganising and shrinking is part of the same command!
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
October 7, 2008 at 4:00 am
Swirl80 (10/7/2008)
but, running a repair / rebuild will take roughly 10mins as opposed to an hour to restore from previous nights backup and all the transaction logs.....and what if the previous nights backup is also corrupt?
You should be running checkDB regularly to check for corruption, so you should always be able to tell when the last good backup was.
If the corruption's just in the nonclustered indexes (if checkDB says that a repair rebuild would be sufficient), then you can manually just rebuild those indexes and there's not need to restore or repair.
If the corruption's in the tables (if checkDB says that a repair allow_data_loss is needed) then you should restore the last good backkup and roll the tran logs forward. Running repair in that situation will cause data loss.
If it's a single page that's damaged, then you can restore just that page and it will be much faster than a full restore.
I've had this issue before where one of the pages in the database had a corrupt line which didn't come to light until a delete was ran against that table - a full select * on that table brought back all records fine!!!! Running a checkDB was what SQL Server recommended so i did and it fixed it so i'd like to run it as a precaution.
Run checkDB without any repair options. That will check for corruption and that's fine to run with replication active. It's intensive and should be run when the server's not in use, but it doesn't require single user mode.
PS: The reorganising and shrinking is part of the same command!
If you can't affect the order, then split them into separate commands. Reorganising indexes before shrinking is a complete waste of time and resources. The shrink will leave the indexes far more fragmented than they were before the reorganise ran.
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 4, 2009 at 8:37 am
Today I had to repair a database which is being replicated.
All I had to do was stop the log reader job.
I had no trouble at all with setting the database single_user and running my checktable.
After the repair I just had to start the log reader job again.
Hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply