Transaction Log Shrinker
Description
Modified version of a T-SQL script for MS SQL Server which allows transation logs / log files to be shrunk whilst
still keeping the database they are attached to on-line, meaning you can increase the amount of space available for
use by other logfiles while not reducing availability of the database.
If you have used any type of commercial database engine you will know that they are relational and transaction based,
and while this makes them incredibly powerful it also means that any wholesale changes to a database hosted on one
of these database engines will result it large logfiles. For Microsoft's SQL Server this causes a problem because
the easiest way to get rid of large logfiles is to simply to
sp_detach the database, delete the physical
log file and then issue a
sp_attach_single_file_db to re-attach the physical file.
While this works wonders it does require taking the database off-line, and in quite a few situations it was never
going to be possible to have that happen. After a fair amount of research I dug up an old article in the KB about
shrinking logfiles in-place, and along with that article there was a script to do just that - if you have been in a
similar situation you will know this is something akin to the holy grail for databases (if you don't already have
something like this already that is), because without the ability to shrink the logs and the individual log files they
are composed while keeping the database on-line, you are forced to take your database offline which means you lose
uptime and the total availability for that database will drop.
What you have here is the modified version of the logfile shrinking script I have been using for some time - the
modifications simply correct problems which caused the script to fail in certain scenarios.
Requirements
- MS SQL Server 7 or 2000
- dbowner / dbadmin priveledges for the target database
Single Compressed Download
Individual Components
Installation & Setup
-
Save the T-SQL script somewhere which will be easily accessible to you via the query analyser, naming
it sql_logshrink.sql (as a suggestion).
User Guide
Open the script up in the query analyser, ensuring that you changing to the correct database (see the
USE
statement near the start) and that you have supplied a valid logfile name (
@LogicalFileName - as the
script suggests issue
sp_helpfile to get a list of these names). These two items simply make sure that
you are going to perform the shrink on the object you expect to affect.
Next check that
@MaxMinutes and
@NewSize are set to values suitable for yourself - they
represent the maximum run-time of the script (in whole minutes), and the ideal size for the logfile (in whole MB).
You are now ready to shrink a logfile.
When you exeucte the script you are presented with a summary of the logfile you have selected to work with, followed
by some SQL status messages. Normally the first time this is executed on a database it simply truncates off the
unused space and doesn't attempt to claw back anything extra from the database engine, with subsequent executions
the script will start padding up the logfile until a page boundary is reached and a chunk of log space can be
recovered.
This script will finish when it either recovers a sufficiently large amount of space, runs out of time, forces the
logfile to the ideal size or does a successful truncation - similar to the start of the process on completion it
presents you with a summary of the logfile you selected to work with.