Best practices for Database Maintenance Plans

Question

Are there any best practices for configuring a Maintenance Plan in SQL Server 2008? Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up. Problem I've seen is that the transaction log is still very large. Should I be including a Shrink Database Plan task?

Answer 1

If your log file is growing that large then you are doing at least one of these things wrong:

• if you need point-in-time recovery, then you are not backing up the log frequently enough (and perhaps the database too). Perhaps you have created a maintenance plan to back up the database daily or weekly, but haven't configured a maintenance plan that backs up the log.
• if you don't need point-in-time recovery, then you are in full recovery model but shouldn't be. You should be able to switch the recovery model for your database(s) to simple. This will mean that you will only be able to recover to the time of the last full or differential backup, but it also means your log file will stay in check on its own.
• in either recovery model, the problem may be that you have an extremely long-running transaction. As in, someone called BEGIN TRANSACTION, locked their workstation, and went on vacation.

In order to shrink the log for now, if you are in full recovery model and want to stay that way, you should perform these steps and restart your log chain to be safe:

USE [master];
GO
ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- if you are already in simple, remove this line:
ALTER DATABASE db_name SET RECOVERY SIMPLE;
GO
CHECKPOINT;
GO
-- if you're already in simple, or want to stay that way, remove this line:
ALTER DATABASE db_name SET RECOVERY FULL;
GO
BACKUP DATABASE db_name TO DISK = 'path' WITH INIT, COMPRESSION;
GO
USE db_name;
GO
DBCC SHRINKFILE(N'db_name_log', 30); -- pick some size that makes sense
GO
USE [master];
GO
-- if recovery is full, re-init log chain, otherwise remove this line:
BACKUP LOG db_name TO DISK = 'path' WITH INIT, COMPRESSION;
GO
ALTER DATABASE db_name SET MULTI_USER;
GO

I commented lines that should be removed if you're already in simple (highly unlikely) or if you want to switch to simple.

Answer 2

You definitely don't want any scheduled file shrinking, or else you'll end up with horrible disk fragmentation. Do it manually on a case-by-case basis, and only when you're sure that the excessive growth was a fluke, for example due to log backups failing to run, or some massive ETL that isn't expected to happen regularly.

Make backups often enough (and keep them long enough) to meet the retention requirements of your business/application. Also consider that you should make log backups often enough to keep the transaction log within an acceptable size. In my case, I run log backups hourly on weekdays, but there's plenty of room to adjust the schedule to your needs. Run them more often to reduce log bloat, and less often to make restoring the log sequence less of a pain.

Answer 3

Your transaction log is still very large probably due to the amount of activity. If you would like to be able to restore to point in time(full recovery mode) and control the transaction log, more frequent transaction log backups is the answer.

We have a 300 gb OLTP database with 30 gb allocated log space. I have a job in place to take log backups every 30 min and that keeps the log file well under the allocated space (even during the monthly index rebuilds. The log backup sizes during rebuilds will be large, though).

Shrinking is not a good practice at all.