Job log may contain the following:
- MSSQL: error: can't get configuration for backup of database 'XXX' on DEFAULT server instance: GetConfiguration timed out after 300000ms MSSQL: backup of database 'XXX' on DEFAULT server instance failed
Check debug-level job message log for:
MSSQL: information handler (instance name , db name XXX): BACKUP LOG cannot be performed because there is no current database backup.NOTE: to set a client's debug level, right-click client from Clients->Summary panel and select Advanced->Set Debug Level. Enter a large-ish value (such as 100) into the Debug Level text box, then click the OK button. Run offending job again with new debug level in order to get additional log messages.
MSSQL: information handler (instance name , db name XXX): BACKUP LOG is terminating abnormally.
Check SQL Server error log for:
- Error: 3041, Severity: 16, State: 1. BACKUP failed to complete the command BACKUP LOG Connect. Check the backup application log for detailed messages.
- BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.
If you see this message in the error log, TAKE NOTE of the time(s) it has occurred! The time(s) will be useful when reviewing maintenance tasks in the "Fix" section below...
NOTE: to find the ERRORLOG file, look in the "LOG" folder of a particular SQL Server instance. This is usually something like "C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG" where "MSSQL.x" is the instance ID for the SQL Server instance.
Check Windows Application Event Log on client for:
Error 12/15/2009 5:50:04 AM MSSQL$INSTANCENAME Backup 3221228513 NT AUTHORITY\SYSTEM BACKUP failed to complete the command BACKUP LOG XXX. Check the backup application log for detailed messages.and/or
NEED TO ADD ENTRY SHOWING "BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG" MESSAGE!!!
The requested backup cannot be completed by the SQL Server because the log chain has been broken. This is likely caused by a conflict from another tool or service, including maintenance tasks scheduled through the SQL Server Management Studio and the associated Maintenance Wizard! NTBACKUP can also cause this issue if it is enabled and doing a system state backup (we have an upcoming NTBACKUP plugin scheduled for 3.4 that will negate this problem)
Remove conflicts that are causing the database's log chain to become broken. In most cases, the maintenance tasks scheduled for the server being backed up should be reviewed and removed (or inactivated) if determined to be at fault. Follow the steps below to review/remove maintenance tasks using SQL Server Management Studio:
- Open SQL Server Management Studio on server having the backup problems
- Locate the instance containing the "problem" database(s) inside the Object Explorer pane (usually along the left side of the application window)
- Find the SQL Server Agent folder for the located instance
- Make sure the Object Explorer Details tab is open in the main display area and click on the Jobs folder under SQL Server Agent
- If jobs exist for the instance, it will look something like this:
- Look for offending jobs such as "Truncate XXX Transaction Logs" (this particular Maintenance Task causes log backups (incrementals) to fail! (HINT: be particularly suspicious of jobs that are scheduled to run at the SAME TIME as the messages in the SQL error log(s) whose time(s) you noted from the steps above)
- Disable or delete offending jobs; you can safely disable a job by editing the job's properties:
- Immediately run a full backup on the database(s) for which offending jobs were disabled/deleted (this will prevent loss of transaction data from the lack of successful log/incremental backups)
- If this is NTBACKUP related:
- The only way to stop NTBACKUP from doing this is to disable to VSS writer responsible. The user will have to disable the MSDE Writer and/or the SQL Server Writer (their OS/SQL version determines which one they will have).