Aug

(DBA TIPS) Maintenance Plan to avoid Transaction log file growth

General problem faced by most the database server is growth in transaction log file. This causes less free memory space on database server.

To recover from a situation where the transaction logs grow to an unacceptable limit, you must reduce the size of the transaction logs. To do this, you must truncate the inactive transactions in your transaction log and shrink the transaction log file.

Truncate the inactive transactions in your transaction log

When the transaction logs grow to an unacceptable limit, you must immediately back up your transaction log file. While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space.

Shrink the transaction log file

The backup operation or the truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file after the backup.

How to backup & Shrink the transaction log file?

Go To the SQL Server->Management -> Maintenance Plan & Right Click -> Click on ‘New Maintenance Plan’

Give Name to the Maintenance Plan

Give Name to the Maintenance Plan
Go to the menu option Views-> Toolbox

Drag Backup Database Task from tool box to maintenance plan

Right click and click on Edit option

Select database for backup click on ok button

Select Backup Type as Transaction Log & set other parameters like folder path etc. & click ok

Drag Maintenance Cleanup Task : To clean up old transaction log file backup from backup folder. Here you can specify same backup folder and set number of days parameter, this task will automatically delete old transaction log file from specified backup folder.