Job failed "Transaction Log Backup Job for DB Main

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Job failed "Transaction Log Backup Job for DB Main

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-04-12 : 09:29:43

Job failed:
Transaction Log Backup Job for DB Maintenance Plan 'ASPState Transaction Log Backup'

From Last few days my transactional log is failed during some specific time I search in history it's showing me the following msg

"The job failed. The Job was invoked by Schedule 1143 (Schedule 1). The last step to run was step 1 (Step 1)."

"Executed as user: *******\********. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."

Please help me to resolve this issue
Your help highly appreciated.

Yogesh V. Desai. | SQLDBA|

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-12 : 09:44:38

If you have your maintenance plan setup to create a log files, look at that file for that run to see the error.

You should also look in the SQL Server Error log to see if any errors are reported at the time the job failed.

You can also look in the Windows Application event log to see if there are any errors reported there.

CODO ERGO SUM

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 09:59:28

Try to backup using the following sp and see if it works:

CREATE PROCEDURE BackupTransLog
@dbName VARCHAR(255) ,
@physicalTLogBackupFileName VARCHAR(255)

AS

BEGIN

DECLARE @physicalTLogBackupFileNameFinal VARCHAR(255)
DECLARE @DateTime VARCHAR(255)
DECLARE @logicalTLogBackupFileName VARCHAR(255)
DECLARE @backupDeviceName VARCHAR(255)

SET @logicalTLogBackupFileName = @dbName+'_Log'
SET @backupDeviceName = @dbName+'LogBackup'

SET @DateTime = (SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(17), GETDATE(), 120), '-', ''), ' ', ''), ':', ''))

SET @physicalTLogBackupFileNameFinal = @physicalTLogBackupFileName + @dbname+'_tlog_' + @DateTime + '.TRN'

IF EXISTS(SELECT TOP 1 * FROM master..sysdevices WHERE name = @backupDeviceName)
BEGIN
EXEC sp_dropdevice @backupDeviceName
END

EXEC sp_addumpdevice 'DISK', @backupDeviceName, @physicalTLogBackupFileNameFinal

BACKUP LOG @dbName TO @backupDeviceName

END

GO

For example, assuming your dbname is 'MyDB' and you have a folder on your hard disk, drive E, named BACKUP:

EXEC BackupTransLog @DBName = 'MyDB', @TransLogBackupLocation = 'E:\BACKUP\'

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-12 : 11:13:20

Check sql server log and plan's log, should have detailed reason there.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-04-13 : 00:34:06

Hi thanks every body for your precious time,I search in all log files I have found some error messages

BACKUP failed to complete the command BACKUP LOG [DBName] TO DISK = N'D:\mssql\Backup\DBName_Log_200704120400.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Source Spid 91
Downgrading backup buffers from 960K to 64K

Source Spid 107
Error: 515, Severity: 16, State: 2

Windows Application Event Log Entry:

Event ID:17055
3041 :
BACKUP failed to complete the command BACKUP LOG [DBname] TO DISK = N'D:\MSSQL\Backup\DBName _tlog_200704130800.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Need some helps to resolve this issue permanatly

Yogesh V. Desai. | SQLDBA|

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources