SQL Server log abckups | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server log abckups

I have 2 servers et up in a log ship scenario. Every hour, I backup the tran log and restore it on the other server. The log backup file normally ranges from 100 K to 200 Meg. On Sunday AM, the log backup file goes to 12 Gig. The database is 19 Gig normally and the log file is about 4 Gig normally. The only other thing I see on Sunday mornings is the Optimization and Integrity check job. Any ideas on why somethign like this is happening?

Optimization, like reindex, will grow log because you have full recovery model and database change (a lot) after optimization. Tha’s wy your log grow.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
True and you must enable the transaction log backup schedule more frequently.
http://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 for reference. If possible you can switch the recovery models from FULL to SIMPLE while reindexing and optimization job runs and then perform full backup then re-enable transaction log backup jobs. Satya SKJ
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.
Would I necessarily have to do a full backup after the Optimization job is done?

In case if you change the recovery model from FULL to SIMPLE and in order to continue the log backups you must perform the full backup. Otherwise it is not needed and in this case it is better to review the log size and set it up. Satya SKJ
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.
]]>