April 5, 2013 at 8:48 am
I have a job that runs every two hours starting at 7 AM and ending at 5 to back up my transaction logs. The issues is for some reason it fails, but when you look at the even log it looks like every "user database" log file was created successfully, but it has a huge red x next to it. I then selected Log to table from the advanced options of the job just to see if I could find more information, and there it was my log file couldn't be backed up because there is no current database. I checked to see if the database was in simple mode, and it isn't. It's in full mode. I re-ran a full back up of the database it when off with out a hitch, but when I started the transaction log job again, it fails at the same spot. I did notice that the database has two log files attached to it. I am not sure what I am missing. Can anyone help?
BACKUP LOG successfully processed 40 pages in 0.031 seconds (10.372 MB/sec). [SQLSTATE 01000]
Processed 0 pages for database <Database name>, file <Database_Log 1>on file 1. [SQLSTATE 01000]
Processed 76 pages for database <Database name>, file <Database_log 2> on file 1. [SQLSTATE 01000]
BACKUP LOG successfully processed 76 pages in 0.055 seconds (11.245 MB/sec). [SQLSTATE 01000]
Msg 4214, Sev 16, State 1, Line 1 : BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000]
Msg 3013, Sev 16, State 1, Line 1 : BACKUP LOG is terminating abnormally. [SQLSTATE 42000]
April 5, 2013 at 9:30 am
Most likely someone's switched the DB to simple recovery and back to full since the last full backup. As such, there's no current database backup to base the transaction log backups on, so they will fail. Someone could also have explicitly truncated the log (backup log with truncate only)
Check for anything switching the DB to simple/full (it'll be in the error log), check for explicit log truncations (they're not in the error log, so you'll need a profiler trace). check what exactly the backup jobs do. I've before now seen a full backup job that ran a backup then switched the recovery model straight afterwards.
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 5, 2013 at 10:00 am
When you say check the event logs, do you mean the windows event logs?
April 5, 2013 at 10:31 am
the full backup runs in full model. Also I don't see anything truncating the log file. this is really frustrating because all of the other databases are running smoothly just this one is not working, and from the error log I posted it appears to be an issue with the second log file. The database has two log files.
April 5, 2013 at 12:58 pm
The number of log files is irrelevant.
What, exactly, does the log backup job do? Post the sql script from it if you can.
There are two common causes of this error:
Running a log backup after a switch to simple and back to full without taking another full backup
Explicitly truncating the log and than not taking a full backup.
The first will leave evidence in the SQL error log, the second won't iirc.
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 5, 2013 at 2:54 pm
That's what every forum I've seen has said. As near as I can tell, It hasn't been changed from simple back to full, but just to make sure I ran a full back up and it is still giving me that error. I looked to see if anything is truncating the log file, but I was not able to find anything similar to that.
Here is the script:
declare @sql varchar(2000),
@dbname varchar(255),
@servername varchar(255),
@datestr varchar(50)
-- you must add entry for this variable
if (select serverproperty('instancename')) is null
begin
set @servername = (select @@SERVERNAME)
end
if (select serverproperty('instancename')) is not null
begin
set @servername = (select convert(varchar(200),serverproperty('instancename')))
end
declare @date datetime
set @date = GETDATE()
set @datestr = CONVERT(char(4),datepart(YEAR,@date)) + right(N'0' + convert(nvarchar(2), month(@date)), 2) + right(N'0' + datename(d, @date), 2) + right(N'0' + datename(HOUR, @date), 2) + '0000'
declare c_1 cursor for select name from master.sys.databases where state_desc = 'online' and recovery_model_desc = 'full' order by name
open c_1
fetch next from c_1 into @dbname
while @@FETCH_STATUS <> -1
begin
set @sql = 'backup log ['+@dbname+'] to disk = ''\\svratlbackup01\sqlbackups011\'+@servername+'\'+@dbname+'_'+@datestr+'.trn'' with init'
exec(@sql)
fetch next from c_1 into @dbname
end
close c_1
deallocate c_1
go
April 5, 2013 at 2:58 pm
For the database in question, what is the current recovery model?
April 5, 2013 at 3:21 pm
It's Full and as near as I can tell, always has been.
April 5, 2013 at 3:55 pm
Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?
What version, since it's clear now that it's not a SQL 2000 database?
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 5, 2013 at 4:15 pm
GilaMonster (4/5/2013)
Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?What version, since it's clear now that it's not a SQL 2000 database?
I viewed the error log and this is what I saw:
2013-04-05 09:39:45.75 Backup BACKUP failed to complete the command BACKUP DATABASE MSNDirectPay. Check the backup application log for detailed messages.
There aren't any other jobs running that have anything to do with the log files. This is on SQL Server 2005.
April 5, 2013 at 4:26 pm
Run a trace in between the full backup and log backups. Capture things and look for any backup or truncate messages.
Something must be breaking the chain if this is not working.
If you manually run a log backup when this happens, what is the result?
April 5, 2013 at 5:18 pm
if I try to manually run it, it tells me that it doesn't have access to the file
April 5, 2013 at 5:45 pm
mqbk (4/5/2013)
GilaMonster (4/5/2013)
Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?What version, since it's clear now that it's not a SQL 2000 database?
I viewed the error log and this is what I saw:
2013-04-05 09:39:45.75 Backup BACKUP failed to complete the command BACKUP DATABASE MSNDirectPay. Check the backup application log for detailed messages.
So your full backups are failing?
If that's the case, and this wasn't an isolated failure, there may well not be a current database backup. What backup tool are you using for backups there? That's not a native backup failure message.
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 6, 2013 at 8:43 am
GilaMonster (4/5/2013)
mqbk (4/5/2013)
GilaMonster (4/5/2013)
Check the SQL error log. Are you seeing recovery model changes? Do you have any other scheduled jobs that do anything to the log (including truncate it)? Do you have any 3rd party backup tools running against this database?What version, since it's clear now that it's not a SQL 2000 database?
I viewed the error log and this is what I saw:
2013-04-05 09:39:45.75 Backup BACKUP failed to complete the command BACKUP DATABASE MSNDirectPay. Check the backup application log for detailed messages.
So your full backups are failing?
If that's the case, and this wasn't an isolated failure, there may well not be a current database backup. What backup tool are you using for backups there? That's not a native backup failure message.
I have actually run several successful full backups since that message. I have checked it by restoring it onto out development environment. I am using the native backup client
April 6, 2013 at 9:08 am
That however is not a native backup error. You have a 3rd party product also backing the DB up? What is it?
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 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply