Script to Shrink Log Files

This script will take the name of a specific log file, its database, and a desired file size, and will then proceed to compress the given log file.

All that needs to be entered is the “tx-logname”, “DESIRED_FINAL_SIZE_IN_MB”, and “basename”.

Author: Conrado Pacheco

DBCC SHRINKFILE (<tx-logname>  , <DESIRED_FINAL_SIZE_IN_MB>,TRUNCATEONLY )
go
BACKUP LOG  <basename> WITH TRUNCATE_ONLY
go

drop table mytable
go

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO
SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
UPDATE MyTable
SET MyField = MyField
WHERE PK = 1 /* Some criteria to restrict to one row. */
SELECT @Index = @Index + 1
END
SET NOCOUNT OFF
go

DBCC SHRINKFILE ( <tx-logname> ,<DESIRED_FINAL_SIZE_IN_MB>, truncateonly )
go
BACKUP LOG < basename > WITH TRUNCATE_ONLY
go

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose...Disclaimer Continued

Back to Database Journal Home