Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup Log cannot be performed because there is no current database backup


Backup Log cannot be performed because there is no current database backup

Author
Message
mqbk
mqbk
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 337
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]
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mqbk
mqbk
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 337
When you say check the event logs, do you mean the windows event logs?
mqbk
mqbk
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 337
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mqbk
mqbk
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 337
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24261 Visits: 37981
For the database in question, what is the current recovery model?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
mqbk
mqbk
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 337
It's Full and as near as I can tell, always has been.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mqbk
mqbk
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 337
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search