Backup transaction log file

Question

Backup transaction log file

Niyala

SSCrazy

Points: 2778
More actions
January 26, 2009 at 8:00 am

#125947

Hi all,
I got a message from our system monitoring saying I have to backup the transaction log file since it was full. However, I have got MSSQL job that has been running for a long time and that has been doing the backup tasks. Anyway, what does it mean that the transaction log file is full? I have sufficient hard disc space and I allowed the log file unlimited growth. Can anyone explain to me how such message should be treated?
Thanks for your hint
Niyala

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd say the first thing to do is to double-check the settings on your log file, and make sure it's actually allowed unlimited growth and that it's actually on a drive that room to expand. Just worth double-checking.

The other thing I'd look at it is are the log backups actually happening. Not database backups, log backups. Different thing entirely.

Let us know what you find on those two things, and we might be able to help further if those don't handle it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

Has your job been doing full backups, or transaction log backups? In full recovery, you need both.

Please read through this - Managing Transaction Logs[/url]

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 3

Hi,

I double checked my settings of the database as well as my disk space.

My findings are:

I have unrestricted growth of the logfile.

The backup job is running, even after that particular event.

The full-backup job is going perfectly well

I have sufficient space on the back up disk

And yet I do not understand why I got this message of 'log file is full'.

Could there be any other reason for this?

Thanks

Niyala

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

Niyala (1/27/2009)
The backup job is running, even after that particular event.
The full-backup job is going perfectly well

Do you have transaction log backups?

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 5

Yes, indeed I have transaction log backup

Thanks

Niyala

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

How often? Is it succeeding?

It's possible that the error came about when the log had to grow and the grow took too long. What's the auto-grow settings on the log file and what's its size?

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 7

Many thanks.

I allowed 10% auto-grow.

Regards

Niyala

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

How often are you doing log backups and are they succeeding?

How big's the log file?

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 9

Hi,

I am doing every 60 minutes and so far all is working well.

Regards

Niyala