September 24, 2008 at 4:44 am
I have a scheduled job which has registered several failures, all as follows:
[/quote]Date 23/09/2008 11:00:00
Log Job History (DBA Transaction Log Backup)
Step ID 1
Server HOST
Job Name DBA Transaction Log Backup
Step Name Backup Transaction Log
Duration 00:01:34
Sql Severity 16
Sql Message ID 3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: domain\user. ...ups.vbs" "E:\MSSQL.1\MSSQL\Backup" CSD21_1100 1 //T:1800 //nologo //B [SQLSTATE 01000] (Message 0) Backup Log CSD21 E:\MSSQL.1\MSSQL\Backup\CSD21_1100_20080923.trn [SQLSTATE 01000] (Message 0) Processed 6 pages for database 'CSD21', file 'CSD21_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 6 pages in 0.014 seconds (3.035 MB/sec). [SQLSTATE 01000] (Message 3014) No SAN Backup [SQLSTATE 01000] (Message 0) cscript "E:\MSSQL.1\MSSQL\Backup\DeleteSurplusBackups.vbs" "E:\MSSQL.1\MSSQL\Backup" KPIMaster_1100 1 //T:1800 //nologo //B [SQLSTATE 01000] (Message 0) Backup Log KPIMaster E:\MSSQL.1\MSSQL\Backup\KPIMaster_1100_20080923.trn [SQLSTATE 01000] (Message 0) Processed 0 pages for database 'KPIMaster', file 'KPIMasterNew_log' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 0 pages in 0.003 seconds (0.000 MB/sec). [SQLSTATE 01000] (Message 3014) No SAN Backup [SQLSTATE 01000] (Message 0) ... The step failed.[/quote]
My question is... what failed? Everything seems to be success until the end where a failure is logged. How can I track this back?
September 24, 2008 at 7:58 am
Is the job doing performing multiple actions in a single step? If so I'd break out each action in it's own job step and then SQL Server Agent will tell you which step failed.
In your case what is the next action after the last successful action? That would be the one that failed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2008 at 11:01 am
In the SQL Server log I have a corresponding log (around the same time) recording a backup failure and to see the backup application log for more detail. I assumed by this it meant the SQL Agent job handling backups, which appears to be this DBA Transaction Log Backup job. However, neither seems to note WHY the job failed, only that it did.
It has since started working, but I'd like to know why it ever failed. Obviously I can't go back and break the job into pieces for the time it failed, is there any other way to get more information about the failure?
I'm not overly familiar with SQL Agent jobs, I can see the properties of the job, and that the target is the local server, but it doesn't appear to run against every database. I'm unsure whether this is because they're retired databases and not generating transaction logs, or because it's being targeted at specific databases in some way. How would you do the later?
September 24, 2008 at 11:15 am
Is the recovery model for KPIMaster database set to SIMPLE?
Check whether any such recovery model change was made for any database which is included under the list of databases backed up by this job script.
MJ
September 24, 2008 at 11:50 am
While my knowledge is iffy I believe the error from this job relates to the following SQL Server log Backup messages:
Date23/09/2008 11:00:02
LogSQL Server (Archive #1 - 23/09/2008 18:24:00)
SourceBackup
Message
Log was backed up. Database: CSD21, creation date(time): 2008/02/14(11:00:04), first LSN: 1072:520:1, last LSN: 1072:603:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL.1\MSSQL\Backup\CSD21_1100_20080923.trn'}). This is an informational message only. No user action is required.
Date23/09/2008 11:00:32
LogSQL Server (Archive #1 - 23/09/2008 18:24:00)
SourceBackup
Message
Log was backed up. Database: KPIMaster, creation date(time): 2008/07/16(13:47:38), first LSN: 45:368:1, last LSN: 45:368:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL.1\MSSQL\Backup\KPIMaster_1100_20080923.trn'}). This is an informational message only. No user action is required.
Date23/09/2008 11:01:03
LogSQL Server (Archive #1 - 23/09/2008 18:24:00)
SourceBackup
Message
Error: 3041, Severity: 16, State: 1.
Date23/09/2008 11:01:03
LogSQL Server (Archive #1 - 23/09/2008 18:24:00)
SourceBackup
Message
BACKUP failed to complete the command BACKUP LOG OpManagerDB. Check the backup application log for detailed messages.
Date23/09/2008 11:01:34
LogSQL Server (Archive #1 - 23/09/2008 18:24:00)
SourceBackup
Message
Log was backed up. Database: PayBase8_5, creation date(time): 2008/01/21(10:15:08), first LSN: 22:18845:1, last LSN: 22:18845:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL.1\MSSQL\Backup\PayBase8_5_1100_20080923.trn'}). This is an informational message only. No user action is required.
The problem is the error seems to suggest I should go to the job for more info, but the job isn't spilling any beans.
September 24, 2008 at 12:00 pm
Check the recovery model on OpManagerDB. It has to be FULL or BULK-LOGGED to do a log backup.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2008 at 12:14 pm
I think you're right, it's part of an AdventNet product and they setup their databases with Simple logging by default.
The PayBase8_5 database had the same error an hour later "Error: 3041, Severity: 16, State: 1." but then an hour later was fine an suffered the error intermittently for the rest of the day. I don't believe it was changed from Full to any other form of logging (can that be checked in the logs?), what other reason could there be for such a failure?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply