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 4:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
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?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1439525
Posted Friday, April 5, 2013 5:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 15, Visits: 164
if I try to manually run it, it tells me that it doesn't have access to the file
Post #1439530
Posted Friday, April 5, 2013 5:45 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: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
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 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 #1439536
Posted Saturday, April 6, 2013 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 15, Visits: 164
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
Post #1439584
Posted Saturday, April 6, 2013 9:08 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: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
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 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 #1439585
Posted Saturday, April 6, 2013 9:21 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: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
Since we're going nowhere fast here...

Finish this script (the file name) and then run this trace for a couple of days. It will pick up all backups, all restores and all changes of recovery model. After a couple days, open up the trace output in profiler, see what else is taking full backups, if there are any log backups that explicitly truncate the log and if there are any changes in recovery model.

/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 2013/04/06 17:18:38 */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100;

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 128, 1, @on
exec sp_trace_setevent @TraceID, 128, 6, @on
exec sp_trace_setevent @TraceID, 128, 10, @on
exec sp_trace_setevent @TraceID, 128, 14, @on
exec sp_trace_setevent @TraceID, 128, 11, @on
exec sp_trace_setevent @TraceID, 128, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go




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 #1439588
Posted Saturday, April 6, 2013 2:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 15, Visits: 164
GilaMonster (4/6/2013)
Since we're going nowhere fast here...

Finish this script (the file name) and then run this trace for a couple of days. It will pick up all backups, all restores and all changes of recovery model. After a couple days, open up the trace output in profiler, see what else is taking full backups, if there are any log backups that explicitly truncate the log and if there are any changes in recovery model.

/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler */
/* Date: 2013/04/06 17:18:38 */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100;

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 128, 1, @on
exec sp_trace_setevent @TraceID, 128, 6, @on
exec sp_trace_setevent @TraceID, 128, 10, @on
exec sp_trace_setevent @TraceID, 128, 14, @on
exec sp_trace_setevent @TraceID, 128, 11, @on
exec sp_trace_setevent @TraceID, 128, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go



Will do! thanks. I will let you know the outcome
Post #1439603
Posted Monday, April 8, 2013 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 15, Visits: 164
So I found the issue. Apparently the previous DBA has a job that is changing the database from Full to Simple to do a database optimization. I don't understand why the previous database did that, but what is more confusing is that all of the other databases are going through the same process and they are not being affected. What gives?
Post #1439809
Posted Monday, April 8, 2013 7:24 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: Yesterday @ 4:58 PM
Points: 42,466, Visits: 35,532
Timing of the full backups? Those other databases having diff backups running after the 'optimisation' job?


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 #1439816
Posted Monday, April 8, 2013 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 15, Visits: 164
If we look at the job schedule in terms of yesterday and today.

The job to optimize runs at about 8pm 04/07/2013, at the end it returns them to full. Then a full runs about 12 am which will be this morning 4/8. Then the Trans_log runs at 7 am 4/8
Post #1439822
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse