Automations

DBCC ShrinkFile Workaround 2005

Search Terms:
DBCC SHRINKFILE not working. Here is the solution.
DBCC SHRINKFILE doesn’t work. Here is the solution.

 


--This script WILL shrink the transaction log file. Works every time!
--even if you were having trouble with the Enterprise Manager shrinkfile function, or if you find the
--dbcc shrinkfile just isn't cutting it, or if you are using the dbcc shrinkfile and you are getting the
--error "...log files are in use".
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
--Change to the database you want to use
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
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
--I've used this time, and time again. it's a nice clean/safe resolution to large transaction logs.
--WORKS LIKE A CHAMP! Shrinks space Way way down regardless of how BIG transaction log
--size is already. I use this often in emergency situations all the time. Especially when disk space
--is a factor. Recently I used this on a 20Gb Transaction Log file, and 1 minute later it was done.
--I think you'll be pleased.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s