SQL Server Performance Forum – Threads Archive

Log Shipping Restore Failing Weekly

I have setup a log shipping pair and they were working until Sunday morning every week (2 weeks in a row). I found that there is only job was running at 2am Sunday morning. I think this weekly maintenance job is the culprit. The steps of the jobs are: Step Name: Steps:
Simple Recovery ALTER DATABASE PwhcProd01 SET RECOVERY SIMPLE
DBCC DBREINDEX sp_MSforeachtable ‘DBCC DBREINDEX(”?”) WITH NO_INFOMSGS’
DBCC UPDATEUSAGE DBCC UPDATEUSAGE (‘Database1’) WITH NO_INFOMSGS
DBCC FREEPROCCACHE DBCC FREEPROCCACHE
DBCC CHECKCATALOG DBCC CHECKCATALOG (‘Database1’) WITH NO_INFOMSGS
DBCC CHECKCONSTRAINTS DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS, NO_INFOMSGS
DBCC CHECKDB DBCC CHECKDB (‘Database1’) WITH NO_INFOMSGS
Update Statistics sp_updatestats
Set to Full Recovery ALTER DATABASE PwhcProd01 SET RECOVERY FULL
During this weekly maintenance job running, the logshipping is not running. Now I have the following questions: 1. What are the steps may cause the log shipping out of sync (not workiing)?
2. May I have the log shipping continue to run while the performing DB maintenance?
3. Not setting the DB to simple mode will make the DB maintenance run longer and create BIG log file. Any suggestions?
4. What’s the best practice for running log shipping and long running DB maintenance? Your help will be highly appreciated.

1) Changing recovery models of database causes the log shipping to break.
2) No better do not overlap the 2 jobs at same time, better to stop LS during maintenance window.
3) Changing FULL to SIMPLE why? You can perform DBCC checks even with FULL recovery model with frequent log backups to keep up the size of tlog.
4) All of above.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Yes, you are right. The setting of simple mode and back to full recovery mode broke the logshipping. I have verified that last night. I have run the DBCC (maintenance) with full recovery mode. Now my log file increased 10 times and the maintenance job run time increased 20%. I will add the DBCC shrink file after the DBCC maintenance steps. Just one potential problem is that one day our log drive may run out of space during dbcc maintenance and crash the database. The increase of run time also may post problems. It may "eats" into our online window. When our database continues to grow and the maintenance job may still be running when our online users login. Any other alternative or suggestion?

Why not test the transaction log sizing activitiy during the scheduled job execution over a period of time in order to set a sensible size to Tlog, rather than shrinking and other jobs are trying to increase. I believe obtaining storage is not a problem now a days and its better to add more disk space in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

The reindex is causing the log file to grow. I have experienced the same issue, the transaction log will go from 12MB to over 3gigs on our database. The subsequent log shipping then takes a long time to complete. By setting recovery to simple the reindexing is not logged, so it prevents the growth. However it de-syncs logshipping. If you want to keep the size down my suggestion is after you set recovery to full again immediately do a full backup to the server you are sending logs to (if that is viable). From that point on your log shipping should work. Here’s what we do:
0 – disable logshipping
1 – local full backup (if viable) You will ONLY be able to restore to this point until you get log shipping going again.
2 – set simple
3 – reindex
4 – set full
5 – full backup to server receiving log shipping
6 – enable log shipping hope this helps

Thanks breckar. This works great with a smaller database. We are trying to avoid nightly backup and refresh to the receiving database. I agree with satya that we have allocated enough space for the log. I just concern about some day there may be a proceess running and greatly increases the log file to a unreasonable size. Thanks for everyone’s input.

]]>