January 3, 2002 at 3:42 am
I keep getting a message telling me my transaction log is full and to back it up. I have backed it up from the Enterprise Manager but this doesn't reduce the size of the log file and it won't allow me delete it. I must be missing something, can anyone help?
Thanks, Jane.
January 3, 2002 at 3:53 am
backing up the transaction log removes the inactive portion but does not physically reduce the file size - use DBCC SHRINKDATABASE or DBCC SHRINKFILE to do this. If this is a test or development system you might want to use truncate log on checkpoint in SQL 7 (Simple recovery for SQL 2000). I experienced one case where the log could not be shrunk - in this scenario I detached the database and physically deleted the log file then reattached the database - a blank log is created. You shouldn't need to do this and it will remove the active portion of the log which violates a recovery plan and must therefore be followed by a full database backup. To sum up: use DBCC SHRINKFILE.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 3, 2002 at 3:55 am
You only need to detach the database if the log file is over 1GB in size (sql doesn't shrink logs over this size)
Steven
January 3, 2002 at 3:59 am
Thanks Steven - that's the first time I've heard a decent explanation of that - one for the memory bank.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 3, 2002 at 4:02 am
Thanks everyone, I'll try SHRINKDATABASE.
Jane
January 3, 2002 at 5:25 am
Steven, just got around to reading your post - is the 1G thing documented, or something you have discovered?
Andy
January 3, 2002 at 6:00 am
Its was discussed on a previous thread (less than a couple of months ago if I remember correctly).
I had the same problem a while back, with a 30gb logfile. Hence remember the posting
Steven
January 3, 2002 at 7:04 am
quote:
You only need to detach the database if the log file is over 1GB in size (sql doesn't shrink logs over this size)
I don't believe this is correct based on personal experience. What could stop a log from shrinking is that the active portion of the log is at the end of the file. Once you get the active log back to the front, DBCC SHRINKFILE() should be able to bring the log file back in line.
I ran into the case a month or so ago where a transaction log had gotten to 3GB. One of the groups in our bank was using a 3rd party app that used SQL Server as a backend. It had been put in place before the DBA group was formed... hence the log size. It shrunk back nicely to 250 MB, which was the size I specified. However, it did take a little work getting the active portion to the front. Steve Jones has posted a script several times which does all this in an iterative manner until the log is shrunk appropriately.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 3, 2002 at 7:13 am
Thanks Brian, makes sence.
Just looked at MSDN. Q256650 also has a script for creating dummy transactions
Steven
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply