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