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

Log backup Fails Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 7:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:02 PM
Points: 81, Visits: 914
I have full backup scheduled daily at evening 5 PM.
Differential occurs daily after every 3 hours throughout the day.
Log backups are scheduled after every 15 minutes.

But the scheduled after 2 am till 3 am for log backup starts failing stating 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.
Post #1379122
Posted Tuesday, October 30, 2012 9:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:02 PM
Points: 81, Visits: 914
I got it there is job which runs at 1:45 which truncates the log and so the log backup starts failing and it resumes once differential is completed at 3:00 am after 3 am log backup are successfull.
Post #1379140
Posted Tuesday, October 30, 2012 10:39 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:51 PM
Points: 112, Visits: 1,209
This error occurs when someone has run a process that has broken the log chain. Probably ran the command BACKUP LOG [Jul_Reports] WITH TRUNCATE_ONLY, or the database was switched to SIMPLE recovery and then back to FULL.

Once the log chain is broken, you cannot perform transaction log backups until the log chain has been re-established. To do that, you need to perform either a full or diff backup on that database.

If you don't need the database in full recovery, switch to simple and stop performing transaction log backups.


SQL Database Recovery Expert
Post #1379145
Posted Wednesday, October 31, 2012 1:54 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 @ 6:00 AM
Points: 42,764, Visits: 35,858
mahesh.dasoni (10/30/2012)
I got it there is job which runs at 1:45 which truncates the log and so the log backup starts failing and it resumes once differential is completed at 3:00 am after 3 am log backup are successfull.


Don't manually truncate your log. You're breaking your log chain and reducing the options you have for restoring that DB in the case of a disaster.

Please read through this - Managing Transaction Logs



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 #1379179
Posted Wednesday, October 31, 2012 6:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
you have scheduled transactional log backup then why there is job for truncate log file

-----------------------------------------------------------------------------
संकेत कोकणे
Post #1379272
Posted Friday, November 9, 2012 3:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:26 AM
Points: 2,840, Visits: 3,963
sanket kokane (10/31/2012)
you have scheduled transactional log backup then why there is job for truncate log file
+1


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1382918
Posted Wednesday, December 12, 2012 12:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
hi mahesh,

This error occurs when someone has run a process that has broken the log chain. Probably ran the command BACKUP LOG [Jul_Reports] WITH TRUNCATE_ONLY, or the database was switched to SIMPLE recovery and then back to FULL.

if someone using backup log with truncate_only command in their job
please set below trace flag

3031
3231

it wont break lsn chain of database.
you will get explaination for traceflag on google.


Post #1395481
Posted Wednesday, December 12, 2012 2:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:26 AM
Points: 2,840, Visits: 3,963
Hemant.R (12/12/2012)
hi mahesh,

This error occurs when someone has run a process that has broken the log chain. Probably ran the command BACKUP LOG [Jul_Reports] WITH TRUNCATE_ONLY, or the database was switched to SIMPLE recovery and then back to FULL.

if someone using backup log with truncate_only command in their job
please set below trace flag

3031
3231

it wont break lsn chain of database.
you will get explaination for traceflag on google.


A month old post


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1395522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse