Log backup Fails

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Log backup Fails

Log backup Fails

Rate Topic

Display Mode

Topic Options

Author

Message

mahesh.dasoni

Posted Tuesday, October 30, 2012 7:32 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 81, Visits: 859

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

mahesh.dasoni

Posted Tuesday, October 30, 2012 9:01 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 81, Visits: 859

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

prettsons

Posted Tuesday, October 30, 2012 10:39 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 99, Visits: 1,025

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 Smile

Post #1379145

GilaMonster

Posted Wednesday, October 31, 2012 1:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 37,669, Visits: 29,922

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

sanket kokane

Posted Wednesday, October 31, 2012 6:14 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 11:21 PM
Points: 323, Visits: 961

you have scheduled transactional log backup then why there is job for truncate log file Blink

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1379272

Bhuvnesh

Posted Friday, November 09, 2012 3:33 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

sanket kokane (10/31/2012)

you have scheduled transactional log backup then why there is job for truncate log file Blink

+1

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1382918

Hemant.R

Posted Wednesday, December 12, 2012 12:59 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 45, Visits: 174

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

Bhuvnesh

Posted Wednesday, December 12, 2012 2:30 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

Hemant.R (12/12/2012)

A month old post Hehe

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1395522

« Prev Topic | Next Topic »

Permissions