SQL Server Performance Forum – Threads Archive
Building a maintenance plan
Here’s our maintenance plan for a production database server. Hosting company backups: Full backup to tape each Sunday, diff backups to tape Mon-Sat. With the maintance plan on SQL Server 2005:– Integrity check, full table backup, clean up history, maintenance cleanup at 2AM each WED and SUN, starting 3/14/07. History, old backups, and trn files more than 7 days old are removed.
– Backup transaction logs every 4 hours each day.
– Update statistics at 2AM every MON, TUE, THU, FRI
– Full rebuild of indexes at 2AM every other SAT As soon as I figure out how, I want to turn on detailed logging of work done in the maintenance plan. I also want to add e-mail notification of any failures. This is a first step. I’m wondering if I should do transaction log backups more often, or if that will affect performance. Recommendations, ideas?
Bryan According to me the interval of taking log backups depends upon your environment. If your environment is highly tansactional and you can’t loose even small amount of data, you should have more frequent log backups. If your enivironment is normal and not really very high transactional environment, 3 or 4 hrs interval should be fine Satya
Make sure you test restores from tape. Including transaction log restores to a point in time. Backing up straight to tape makes me nervous, but as long as you are completely sure it’s viable it should be ok. And I would go for more frequent tranny log backups too, for any normal transactional system. Are they going straight to tape too? That itself I wouldn’t recommend. If your tape machine breaks, your database log is going to grow and grow.
The backups in the maintenance plan are all to disk. Unfortunately, on that database server everything is on one partition including Windows Server 2003, SQL Server 2005, all database files, and all backups. It is raid, though. The only separate backups are done by the hosting company onto tape.
Reduce your tlog backup interval and make sure your tlog backups also goes to tape backup…. SQL Server 2005 Books Online "Best Practices for Recovering a Database to a Specific Recovery Point"
http://msdn2.microsoft.com/en-us/library/ms191468.aspx
SQL Server 2005 Books Online "Applying Transaction Log Backups "
http://msdn2.microsoft.com/en-us/library/ms189596.aspx MohammedU.
Moderator
SQL-Server-Performance.com
Thanks to everyone for the help. When talking to the hosting company about the tape backups they perform, they commented that for our largest database the mdf file is 36GB and the ldf file is 18GB, so I should start running shrink before each backup in the maintenance plan to cut down the size of the ldf file. From what I’ve read here, there’s hardly ever a reason to run shrink. Their comment puzzles me. Am I missing anything in my maintenance plan?
]]>