Backup fails, log is full

Question

Backup fails, log is full

Niyala

SSCrazy

Points: 2778
More actions
September 23, 2010 at 2:23 am

#229617

Hi List,
I am running MS SQL Server 2008 SP1 and I have one user Database running on it. The database is in Simple Recovery mode. Surprisingly, the backup job of the database fails with the following error message:
A SQL job failed
Database Backup failed
Could not write a Checkpoint
Database logfile is full
Does anyone have an explanation why this error is coming even though the database is in simple recovery mode?
Thanks indeed in advance for your information.
Kind regards
Niyala

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Simple recovery does not mean that the log will not grow. It does not mean that the log will never fill. It only means that log backups are not required to clear the log. There are several other things that can cause the log space to not be reused.

Please query sys.databases and see what the Log_Reuse_Wait_Desc is for this 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

Niyala SSCrazy Points: 2778 More actions · Answer 2

Hi,

In fact I already checked the log_reuse_wait_desc.

The values returned are "NOTHING". That is why I thought there should be other problems. What else can I do to understand why the error comes up?

Kind regards

Niyaly

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Run DBCC SQLPERF(LogSpace), see what the % used is.

How big's the log? Is there a max size set for it? Is it on a drive with little free space?

How long will the full backups take and is there heavy user activity at the same time?

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

Niyala SSCrazy Points: 2778 More actions · Answer 4

Hi,

The database with problem has the following values:

Log Size (MB): 1093

Log Space Used (%): 2.5

Status: 0

I have a 100 GB LUN with 66 GB Free Space.

The full backup takes pretty long, about 20 minutes.

As the database server hosts the SCOM 2007 R2 database, there is definitely heavy activity since the SCOM is constantly gathering events from monitored servers and clients.

The maximum size was set 2.5 GB

Then what is the solution if the server returns such error even though the database is in simple recovery mode?

Kind regards

Niyala

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I suspect it has to do with the limited size of the log, the duration of the backup and the activity.

Because the full backup needs to include all of the log records since it started (or maybe before), log space cannot be reused during a full backup. Hence, if you have lots of activity and a long running backup, there can be a fair bit of log space used. It may well be that you just don't have enough space allocated.

Consider increasing the max size of the log. 5GB or 10 if you can.

Do you have autoshrink on (or a manual shrink operation)?

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

Niyala SSCrazy Points: 2778 More actions · Answer 6

Hi,

I now configured the log autogrowth by 10% and unrestricted.

Is it better to set fixed growth than unrestricted?

I do the shrinking manually whenever necessary to do so. I read several time that autoshrinking is not recommended as best practice.

Kind regards

Niyala

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Niyala (9/23/2010)
I now configured the log autogrowth by 10% and unrestricted.
Is it better to set fixed growth than unrestricted?

Percentage growth is usually not recommended. Pick an appropriate fixed value. The percentage can be a very large amount when the log gets larger, and that could cause autogrow operations that take a long time or even timeout.

I do the shrinking manually whenever necessary to do so. I read several time that autoshrinking is not recommended as best practice.

Repeated manual shrinking is little better than autoshrink. Stop shrinking your database/log. All you're doing is forcing it to grow again.

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

Niyala SSCrazy Points: 2778 More actions · Answer 8

Hi,

Thanks a lot for all your support. I implemented your suggestions and I will followup the server in the next couple of days.

Kind regards

Niyala