SQL Server 2008 R2 Maintenance - Transaction Log Backups Failing

Can you try running the trans log backup on one database at a time to see if you can narrow it down to a specific database\logfile?  Also are you attempting to do anything else in the same maintenance plan that you are using to backup the logs?

Under SQL Server Agent, expand the Jobs tree and then find the job, right click and View History.

Each job should have a plus sign to the left of it to expand the job.

Click on one of the steps and you should have more information on the error - or look in the LOG directory of your instance installation and have a look at the contents of SQLAGENT.OUT for more details of the error.

Once you get your backups working, have a read of Kimberly Tripp's couple of excellent blog entries on just this topic for shrinking and then regrowing your log files to an appropriate size:

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

As part of my maintenance plan I have:

My transaction log backup is set to run every 2 hours, so about 12 times within 24 hours, which is during times of when queries and realtime database access is occuring, etc.

 -ConsistencyChecks every night 10pm for all databases and include indexes
-Backup Databases eery night 10pm, Full backup for all Databases, verify backup integrity and compress backups
-Update statistics on all databases, all existing statistics, full scan weekly saturdays at 1am
-Rebuild indexes on all user databases the first sunday of every month - reorganize pages with the default amoutn of free space
-Log and History cleanuup: Delete backup files, based on txt extension, older than 3 weeks - every sunday at 6pm

**The last log shrinking job that appears to have ran was almost 2 years ago, the T-SQL statement on that job is:

USE [SQLDatabase]
GO
DBCC SHRINKFILE (N'Test DB Creation_log' , 0, TRUNCATEONLY)
GO

Is this something that I can/should run again? Is this something that can be done at any time or after hours with limited database access? Also how long would it take on a 219GB transacation log file?

----------------------------------------------------------------------
Result of the Log File Job History:

Date            1/8/2013 4:00:01 PM
Log            Job History (DBMaintenance.Transaction Log)

Step ID            1
Server            SQLSERVER
Job Name            DBMaintenance.Transaction Log
Step Name            Transaction Log
Duration            00:00:13
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: INTERNAL.CORP\svc_sql. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:00:01 PM  Progress: 2013-01-08 16:00:03.44     Source: {02BF0A94-27C5-4D0A-8383-4E6F525B27B7}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2013-01-08 16:00:09.78     Source: Back Up Database Task      Executing query "BACKUP LOG [BackupExec] TO  DISK = N'G:\SQLBACKUP\...".: 100% complete  End Progress  Progress: 2013-01-08 16:00:10.03     Source: Back Up Database Task      Executing query "BACKUP LOG [BEDB] TO  DISK = N'G:\SQLBACKUP\Transa...".: 100% complete  End Progress  Progress: 2013-01-08 16:00:10.31     Source: Back Up Database Task      Executing query "BACKUP LOG [DB2] TO  DISK = N'G:\SQLBACKUP\...".: 100% complete  End Progress  Progress: 2013-01-08 16:00:11.12     Source: Back Up Database Task      Executing query "BACKUP LOG [SQLDATABASE] TO  DISK = N'G:\SQLBACKUP\Tr...".: 100% complete  End Progress  Progress: 2013-01-08 16:00:11.49     Source: Back Up Database Task      Executing query "BACKUP LOG [ReportServer] TO  DISK = N'G:\SQLBACKU...".: 100% complete  End Progress  Error: 2013-01-08 16:00:12.40     Code: 0xC002F210     Source: Maintenance Cleanup Task Execute SQL Task     Description: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'E:\Program F..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:00:01 PM  Finished: 4:00:12 PM  Elapsed:  11.482 seconds.  The package execution failed.  The step failed.

Backups seem to be working, looks like it's the maintenance clean up task that's failing:

Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'E:\Program F..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'"

Check the Maintenance Cleanup Task - are you telling it to look in the right location?  Does that location exist?

You're backing everything up to g:\SQLBACKUP but trying to delete files from e:\program ... ?

Ok Great, that did it, I saw a maintenance cleanup task below the transaction log backup job that contained the old path after the backups were moved from E: to G:.

Now onto the other issue...how is the log file so big, if the tranasaction log backups are successful?

This is the TSQL for those transaction log backups:

BACKUP LOG [INTERNAL] TO  DISK = N'G:\SQLBACKUP\Transaction Log backups\INTERNAL_backup_2013_01_08_164427_2599474.trn' WITH  RETAINDAYS = 7, NOFORMAT, NOINIT,  NAME = N'INTERNAL_backup_2013_01_08_164427_2599474', SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

OK see these two links as mentioned above:

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

The transaction log will grow and grow forever unless a transaction log backup is done.

At some stage in the past you've either had a really large transaction or had a while without transaction log backups.

Now that you're doing log backups, there's space in the logfile that can be reclaimed.

With that in mind, have a read of the two blog posts and let us know if you need anything else.

So essentially, after the transaction log backup completes, i'll run a

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY) command and that should shrink the size - how long does this process normally take, I intend on performing tonight  after our full database backup completes is there a timeframe that you can provide estimate , minutes, hours?

Thank you again for all your assistance!

So essentially, after the transaction log backup completes, i'll run a

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)
If you value performance and data integrity, please do not do this, this is a very bad idea.

acperkins:  After a full backup and a log backup (which marks the transaction log as truncated), why wouldn't you be able to do this?

I always take another full backup after doing this, if I'm ever faced with a runaway transaction log.

This is the advice given in the blog posts I mentioned as well (and several others) as the steps involved in shrinking a transaction log file and then growing it again afterwards for both making your log file smaller and dealing with too many VLF's.

After a full backup and a log backup (which marks the transaction log as truncated), why wouldn't you be able to do this?
Actually I am sorry, I may have taken the comment out of context.  I thought the author was suggesting adding this to their scheduled maintenance job, rather than a one off emergency-only situation.

This is the advice given in the blog posts I mentioned as well
Exactly and the part the author did not quote, most likely overlooked (even though you did mention it) and in my opinion the most important part was that the database has to be sized appropriately after shrinking it (Kimberly's step 3).