April 6, 2006 at 7:16 am
Hi all,
I have a problem that is confusing me somewhat. One of my databases (SQL Server v7) is getting an error message to say that the transation log is full. But it is set to automatically grow by 10%, unrestricted file size. Therefore I assume that the server is running out of space.
So I attempted to shrink the log file, but get an error saying the log has to be backed up first. I cannot back up the log as there is not enough room on the server. So I think well I'll try to truncate and then shrink, and the truncate looks successful. but still I can't backup the log. And it is still full as I can't shrink it.
What can I do?
Thanks,
Paula.
April 6, 2006 at 8:10 am
You say you 'assume' the server is running out of space. Have you checked? That's the first thing to do. There are several reasons to get this message.
1. There isn't any more room on the hard drive.
2. The log file is not set to autogrow and/or isn't set to a large enough size.
3. The log file was not set to grow fast enough.
About #3, if you set the log file to grow by a small amount (let's say you set it to 1 MB) and the transaction needs 10 MB of space, the log file can't grow fast enough for the transaction and will give you that message.
So, first check to see if there is room on the drive. If so, then check to see that you allow the log file to autogrow. Make sure it's set for a high enough number (don't use the percentage - that can be bad).
If that all looks good, then you probably will need to delete your log file if you can't truncate it and shrink it. BEFORE YOU DO THAT, do a search on this site for things like:
lost transaction log
deleted transaction log
sp_attach_single_file_db (can also find this in the BOL).
If you try this method, first detach the database and then RENAME the .ldf file and try to move it somewhere else (or back it up to tape). That way if things go wrong, you can recover it.
-SQLBill
April 6, 2006 at 8:51 am
You can do a
BACKUP LOG dbnm
WITH TRUNCATE_ONLY
which blows otu the ability to recover from the Transaction Log until you take another Full DB backup.
However it won't shirnk the fil either. SQL 7 has a known issue with this. However, try using this script
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=26
to hlp you out. Again you will need to take a Full DB backup when you are done.
April 6, 2006 at 9:06 am
Thanks for this. I couldn't do backup log at all, it just didn't like it. I ended up restoring the latest backup, which took us back just enough to allow me to back up the log, truncate and then shrink. But it still only cleared around 300mb, which was a lot less than I had hoped for. And so I will take a close look at the script above and try to clear a more siginificant amount of space now.
So thank you!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply