Huge Log file - Does not shrink

Question

Huge Log file - Does not shrink

savvass

Newbie

Points: 3
More actions
June 24, 2006 at 2:20 am

#60508

I am having trouble managing the log file of an SQL2000 logfile running on W2KSRV.
The database is fairly small (~1.5Gb) but the log file has grown to about 14Gb following a lengthy batch processing session.

I tried to manually shrink the database, manually shrink the data file and log file, and also backup and restore the database. Nothing seems to be able to shrink the log file. (All these tasks were performed with the database in idle).
I was able to recover the space by means of transfering the data from the original database to a copy and then dropping the original database, however this scenario of log file growth is recurring.
Any hints on what might be causing it or how I can shrink the log file?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

This question seems to come up every week or so ...... did you search the existing posts ? Look for "huge log" etc ...

Is your db in FULL or SIMPLE recovery mode ? If FULL, are you backing up the transaction log regularly ? Do you have any open, uncommitted transactions ?

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Are you doing a lot of reindexing? If so, that will cause the log to grow.

-SQLBill

mel-260965 SSC Enthusiast Points: 147 More actions · Answer 3

I Googled this a few years back and came up with an easy shotgun technique.

Download the script for [sp_force_shrink_log] and install it in [master]

Then use the following TQL wrapper for best results...

/*

force shrink log of specified database

*/

use <db_name, sysname, dbname>

go

select * from sysfiles

BACKUP LOG <db_name, sysname, dbname> WITH TRUNCATE_ONLY

GO

checkpoint

GO

WAITFOR DELAY '00:00:05'

DBCC SHRINKDATABASE (N'<db_name, sysname, dbname>', 0,TRUNCATEONLY)

GO

WAITFOR DELAY '00:00:05'

exec sp_force_shrink_log @target_size_MB = 10 , @max_iterations = 10

GO

DECLARE @DBNAME sysname

select @DBNAME = name from sysfiles where fileid = 1

exec ('DBCC SHRINKFILE ('+@DBNAME+')')

go

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 4

Here's another approach you could try:

Use [enter your database here]

go

--Turn off auto truncate log

sp_dboption 'your database here', 'trunc. log on chkpt.', false

go

-- Truncate the transaction log

Backup log [your database here] with truncate_only

go

--Create a temp table to create bogus transactions

create table t1(f1 int)

go

--Load the temp table. This will cause the transaction log to to fill a tiny bit.

--Enough so you can checkpoint, and shrink the tran log.

declare @i int

set @i= 1

while @i < 10000

Begin

Insert t1

Select @i

set @i = @i + 1

End

Update t1

Set f1 = f1 + 1

go

--To get the logical name of the tlog file just use sp_helpfile under the database you want to shrink.

dbcc shrinkfile(logical_filename_Log)

go

--Truncate the log again. This will cause the file to shrink.

Backup log [your database here] with truncate_only

go

--Reactivate auto truncate

sp_dboption 'your database here', 'trunc. log on chkpt.', true

go

--Drop the temp table

Drop table t1

go

sqlgiant SSC Eights! Points: 985 More actions · Answer 5

Savass -

I feel your pain. Since you have done all the standard things to remedy this, here's the short version of what you need to know:

I assume your DB is in FULL Recovery mode.

1. The Transaction Log is a comprised of any number of Virtual Logs.

2. 'EXECUTE DBCC Loginfo' in the problem Database to see the Virtual Log layout. A Status = 2 means that specific virtual Log is active. You cannot shrink the Physical log past an Active Virtual Log.

3. You must wrap the Virtual Logs around to physically move that Active Virtual Log off the end of the file. In SQL Server 2000, you can simply execute BACKUP LOG/DBCC SHRINKFILE multiple times to accomplish this. Each time you run the sequence, run 'DBCC loginfo' to see where your active VLFs are. Eventually they will drop off.

4. Thanks to this guy for his brilliance;

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

I used this method to perform a

DBCC SHRINKFILE(MYDB,'emptyfile') operation, where all Active VLFs had to be removed and it worked splendidly.

Stuart

"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid