Error: 9002 Log file for database xx is full. But it''s not!
-
Hey all,
I maintain an SQL box but am not really a DBA - our core systems are on Oracle and we have an Oracle DBA. Unfortunately a few apps we use only support SQL. So, forgive any silly questions
I'm getting the 9002 error, "The log file for database xx is full. Back up the transaction log for the database to free up some log space"
This particular database is for a crappy web filtering product so I don't care about the log file for recovery.. so, I did a "truncate transaction xx with no_log" to blow the log away. This works, but then 5 minutes later I get the log full error again in Event Viewer.
The log is set to a restricted size of 50mb - I don't want it growing without restriction and filling the disk.
However if I go to Shrink Databases, Advanced, and look at the log file, it shows as Current size: 50mb (cool) and Space Used: 7mb. So... the log file isn't anywhere near full ! But, I'm still getting errors to the contrary. I have other databases all set with a restricted file size and they don't suffer this issue (unless the log is actually full, which is understandable!)
Anyone got any clues for me as to why SQL is reporting the log to be full when it isn't ?
On a side note, is there a way of setting SQL to overwrite old transactions when it reaches the hard log size limit? I back up the database daily, and the transaction logs are not needed for point in time recovery.. at the moment I schedule a weekly log truncate on the databases that do generate lots of transaction logs so that the log doesn't fill and crash - but this seems like a dodgy way of doing things to me.
Cheers
Dave
-
alter database xx set RECOVERY SIMPLE .
This way every ended transaction's logspace is immediatly set available again.
Point in time recovery is not possible. you can only restore fullbackups and differentials.
If the sum transactions use more then 50mb, sqlserver tries to extend your logfile. If that is restricted to the current size, you get the "file full" message, if it cannot extend within reasonable time, it gets a timeout and also throws the "file full" message. If your db already is at simple recovery mode, extend your logfile a bit and launch sqlprofiler trace to capture the sql that generates that much load.
Check books online for more info.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
thanks for the response. Database was already in simple recovery.
i've increased it to 100mb, and done a shrink on the file to the minimum, so it's back to 1mb in size with a max grow limit of 100mb.
will see what happens overnight.
-
If you can, don't shrink the logfile !
Every extend it needs, takes an extra efford of your server.
It only extends if needed, due to transaction load.
It's a good choice to limit it to extend to 100mb, because that helps you controll your diskspace.
The same goes for extendsize. If you set it to x mb, it only extends in chunks of x mb. If you leave it to 10 percent (default) It extends by 10 % of the current size
, so each time more and more,..Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
cheers, thanks for that
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply