Managing Transaction Log Size using a Maintenance Plan [duplicate]

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.

A lot more details about this can be seen in this question:

• Why Does the Transaction Log Keep Growing or Run Out of Space?

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.

Answer 4

Use Maintenance Plan to schedule DB backups in SQL Server.

• For point-in-time recovery, create Full database backup scheduled daily(in midnight) and transaction log backups scheduled every 4 hrs.

• For no point-in-time recovery, change DB recovery model as SIMPLE with database backup scheduled daily (in midnight).

• You can also check the open transactions by using DBCC opentran and then kill that transaction using KILL SPID

• Use DBCC sqlperf(logspace) to get the status of your log file size and the actual space used in this log file. If free space is much more than shrink the database log file.

Answer 5

Even the maintenance back up plans offer advantages, like GUI and support for native jobs, there are some downsides that you should be aware of. You must maintain both maintenance backup plans and Agent jobs in order to maintain consistency and to avoid process collisions. Also, there is no collision detection and your tasks may fail if concurrent tasks are scheduled on the same database(s) at the same time. So, a detailed test must be performed each time you set of modify maintenance plans