Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Backup Log cannot be performed because there is no current database backup Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 8:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 12, Visits: 151
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]
Post #1439262
Posted Friday, April 5, 2013 9:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1439292
Posted Friday, April 5, 2013 10:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 12, Visits: 151
When you say check the event logs, do you mean the windows event logs?
Post #1439332
Posted Friday, April 5, 2013 10:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 12, Visits: 151
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.
Post #1439355
Posted Friday, April 5, 2013 12:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1439432
Posted Friday, April 5, 2013 2:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 12, Visits: 151
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
Post #1439494
Posted Friday, April 5, 2013 2:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 23,002, Visits: 31,490
For the database in question, what is the current recovery model?



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)
Post #1439495
Posted Friday, April 5, 2013 3:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 12, Visits: 151
It's Full and as near as I can tell, always has been.
Post #1439501
Posted Friday, April 5, 2013 3:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
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 2008, MVP
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

Post #1439518
Posted Friday, April 5, 2013 4:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 12, Visits: 151
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.
Post #1439521
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse