April 28, 2009 at 9:13 am
I have a Scheduled Job to backup transaction logs every 15 minutes, and a full backup every night at 10 pm. There are about 25 databases. I sporadically get failures of a few of the transaction log backups with the error message:
"[font="Courier New"]Failed (-1073548784) Executing the query "BACKUP LOG [My_Database] TO DISK = N'E:\\MSSQL.1\\MSSQL\\Backup\\TLogs\\My_Database_backup_200904281108.trn' WITH NOFORMAT, NOINIT, NAME = N'My_Database_backup_20090428110857', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly..[/font]
But that's not true. There is a full backup at 10 pm, and the log backup worked fine from midnight to 10:00 am, then started failing on 3 of the databases. It may work fine again in a few hours.
It seems to be the same 3 or 4 databases each time.
2 days ago it went 24 hours with no errors. 3 days ago, it failed for 6 hours before working again.
I was using Redgate, and just set up a SQL2005 Maint Plan, and both have the same problem.
Thoughts ?
April 28, 2009 at 9:19 am
Were there any changes in the recovery model used by the databases during that time? If there was a change from FULL to SIMPLE and back to FULL, then a FULL BACKUP needs to be run again after the change back to FULL.
April 28, 2009 at 9:21 am
The error is saying that the log has been truncated sometime since the last full or diff backup. Look for jobs that run BACKUP LOG ... WITH TRUNCATE ONLY or which switch the recovery model to simple and back to full, and make sure that no one is manually doing either of those.
Either of those will prevent any log backups from running successfully until another full or diff backup is run
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 9:21 am
Good point. I'm not aware of any changes, but I'll look into it.
April 28, 2009 at 9:27 am
another thought - although i may be barking up the wrong tree here..
how does SQL 2005/2008 know if there has never been a backup? if it's the MSDB backup history tables then SQL server agent may be purging these in your maintenance plan
seems unlikely - i'd rather stake my money on a proc doing a backup log with truncate_only
MVDBA
April 28, 2009 at 10:14 am
michael vessey (4/28/2009)
how does SQL 2005/2008 know if there has never been a backup? if it's the MSDB backup history tables then SQL server agent may be purging these in your maintenance plan
I believe it's stored somewhere in the database header page. Need to check details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply