January 26, 2009 at 8:00 am
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
January 26, 2009 at 8:25 am
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
January 26, 2009 at 9:38 am
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
January 27, 2009 at 1:33 am
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
January 27, 2009 at 3:56 am
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
January 27, 2009 at 3:59 am
Yes, indeed I have transaction log backup
Thanks
Niyala
January 27, 2009 at 4:09 am
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
January 27, 2009 at 5:06 am
Many thanks.
I allowed 10% auto-grow.
Regards
Niyala
January 27, 2009 at 5:17 am
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
January 27, 2009 at 5:52 am
Hi,
I am doing every 60 minutes and so far all is working well.
Regards
Niyala
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply