SQL Server 2008 : Using Alerts to prevent Log disks to go out of space

The size of transaction log file will increase indefinitely if the recovery model of the database is set to Full and we don’t perform Log backups periodically.
With SQL Server Alerts and Jobs we can automate log backups.

• Create a backup device to store backups
• From object explorer develop Server Objects
• Right click Backup Devices
• Click New Backup Device

 

• Give a name and a filename to the backup device

• Click Ok
• Create an operator to receive notification
• Right click Operators folder under SQL Server Agent
• Click New Operator

• Fill the fields of the General tab with the required information

 

• Click Ok
• Create a new Job to backup the transaction log
• Right click Jibs folder under SQL Server Agent
• Click New Job

• Name the job on the General tab

• Create a Transact-SQL Script step on the Job with the script : BACKUP LOG ‘DataBaseName’ TO ‘BackupDeviceName’

• On the Notification tab choose the operator to be notified

• Create an Alert based on the performance counter Percent Log Used
• Right click Alerts folder
• Click New Alert        

• Configure the Alert with the following values
• Name : ‘AlertName’
• Type : SQL Server Performance Counter Alert
• Object : SQLServer:Databases
• Counter: Percent Log Used
• Instance: ‘Database Name’
• Alert if counter : Is greater than
• Value : ‘Type a value < 100’

• On the Response tab choose Execute Job as response, select the Job created on the previous steps and select the operator to be notified.