Script to Shrink Log Files

Tuesday Nov 5th 2002 by DatabaseJournal.com Staff
Share:

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


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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved