Transaction file balooned!

Last Post 28 Mar 2012 08:00 AM by Simon Wilson. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Gee
New Member
New Member

--
26 Sep 2011 05:52 PM
Help! Hope someone can throw me a useful tip here (online searches have been confusing for a novice), I have a transaction file on a SQL2000 server that has suddenly ballooned to 375GB  and is threatening to fill the hard disk.
How can I shrink it and correct what ever caused this? TIA
gunneyk
New Member
New Member

--
27 Sep 2011 04:28 AM
Are you in FULL recovery mode? If so you need to do regular log backups in order to reuse the tran log space. If you don't want full recovery switch it to SIMPLE mode. But you can also have a long running open transaction preventing reuse as well. Run DBCC OPENTRAN() in the context of that db to see if there is an old transaction hanging around. If so find out why and either commit it or kill the connection and it will rollback. Then you can shrink the log fil with DBCC SHRINKFILE.
Gee
New Member
New Member

--
27 Sep 2011 06:15 AM
Hi, thanks for the reply, DBCC OPENTRAN is beyond me at this point, may I somply toggle modes to Simple, then toggle back at a later time?
gunneyk
New Member
New Member

--
27 Sep 2011 07:58 AM
Yes you can switch to SIMPLE mode if there are no open transactions that are causing this but before you switch back make sure you have a plan to backup the log files or it will happen again.
Gee
New Member
New Member

--
27 Sep 2011 10:13 AM
Thanks for the info! I did a transaction only backup last night and noticed the resulting file was only 4GB. I considered restoring this file to overwriting the original, is this an option?
gunneyk
New Member
New Member

--
27 Sep 2011 10:40 AM
The size of the backup has nothigngto do with what I was describing. Again if there is an open tran in there it doesn't have to be large, just open. Run this command and post the output.

USE Yourdb ;

DBCC OPENTRAN() ;

A restore won't help if it is a recent backup it will be the same size log file. If its an old one you lose data.
Gee
New Member
New Member

--
27 Sep 2011 11:34 AM
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
rm
New Member
New Member

--
27 Sep 2011 11:46 AM
If you change to simple recovery mode then change back to full recovery mode, you have to do full backup first. Otherwise sql will not let you do log backup.
Gee
New Member
New Member

--
27 Sep 2011 12:53 PM
Thanks for the help, I have been schooled!
Addision Philip
New Member
New Member

--
28 Sep 2011 12:54 AM
Have a look at this article, http://sql-server-recovery.blogspot...n-log.html Posted by Mark Willium.
gunneyk
New Member
New Member

--
28 Sep 2011 04:51 AM
OMG, please do not read that article. I can't believe Mark wrote and posted soemthign like that. He obviously has no idea how a transaction log really works. The behaviour he describes only happens when you are in FULL recovery mode and do not backup the tran log. His solution is not a solution at all and has all sorts of potential side effects.
Gee
New Member
New Member

--
28 Sep 2011 06:40 AM
Thanks all!
Simon Wilson
New Member
New Member

--
28 Mar 2012 08:00 AM
Please use the following query:

SELECT LOG_REUSE_WAIT_DESC, NAME
FROM SYS.DATABASES

if for your database, it says that there is an "Active_Transaction" then it will not allow log file to shrink. Try to identify the open transaction.

Changing the recovery model will work, but could break the log-shipping if it is there.

Cheers.
You are not authorized to post a reply.

Acceptable Use Policy