SQL Server 2008 R2 Maintenance - Transaction Log Backups Failing
Posted on 2013-01-08
I have a large DB file 146Gb and and even larger Transaction Log file 219GB that is constantly growing.
I have scheduled transaction log backups to run every 2 hours as per DB maintenance plan in an effort to shrink the log file however it's not working and the jobs fail with the following error:
Log Name: Application
Date: 1/3/2013 6:00:07 AM
Event ID: 208
Task Category: Job Engine
SQL Server Scheduled Job 'DBMaintenance.Transaction Log' (0xA16724D22E27E042BD579A5E41365A12) - Status: Failed - Invoked on: 2013-01-03 06:00:00 - Message: The job failed. The Job was invoked by Schedule 14 (DBMaintenance.Transaction Log). The last step to run was step 1 (Transaction Log).
This error is generated every 2 hours, it is set to backup the transaction logs on specific database which includes the one in question. I see in the datatbase selection, there is a warning advising databases with simple recovery will be excluded. - I have verified that the recovery model of the database is set to Full.
I have 2 issues I need assistance with (i'm not very fluent in SQL so bear with me)
-Need to determine why the Transaction Log backup is not completing
-Need to shrink the log /database files without any down time