Scripting Log re-sizing

I know, shrinking log files is bad, but sometimes it is also necessary. Sometimes things go wrong, queries go bad, indexes rebuilds run forever, and any of these events can cause a log file to grow beyond its usual size. When this happens, the best practice is to shrink the file down to it’s smallest possible unit, and then re-grow it in chunks to the desired size. The size of these chunks and the size you grow your log to, are completely down to the nature of the database and its business requirements. The following script doesn’t address these sizes, it simply allows you to get away from the tedious process of growing your log 1 chunk at a time using scripts or the SSMS interface. By simply setting your database, max size and the chunk size you can run and go.

Disclaimer – all growth operations (both data and log) should be coordinated to avoid potential conflicts with user activity.

USE master
GO

--our variables
DECLARE @maxsize_MB INT;
DECLARE @currentsize_MB INT;
DECLARE @growth_size INT;
DECLARE @first_growth INT;
DECLARE @log_name VARCHAR(60);
DECLARE @command VARCHAR(255);
DECLARE @db_name VARCHAR(60);

--the database you are growing, max size of the log and the block size you want to grow it in
SET @db_name = 'AdventureWorks'
SET @maxsize_MB = 2048;
SET @growth_size = 512;

--get the details for our database
SELECT
@log_name = name,
@currentsize_MB = (size * 8) / 1024 -- size is recorded in # of 8KB pages
FROM
sys.master_files
WHERE
database_id = DB_ID(@db_name)
AND
type = 1 --log

--set the initial size up to our growth size
SET @first_growth = @growth_size - @currentsize_MB

--grow the log file until it is at a size we like
WHILE @currentsize_MB < @maxsize_MB
BEGIN

SET @currentsize_MB = CASE
WHEN @first_growth = 0 THEN @currentsize_MB + @growth_size
ELSE @currentsize_MB + @first_growth
END
SET @command = 'ALTER DATABASE [' + @db_name + '] MODIFY FILE ( NAME = N''' + @log_name + ''', SIZE = ' + CAST(@currentsize_MB AS VARCHAR(25)) + ' MB )';
PRINT @currentsize_MB
PRINT @command
EXECUTE(@command)

--ignore this after first growth
SET @first_growth = 0

END

I usually comment out the execute statement to check the growth pattern before running live. Please feel free to add any comments on possible improvements or concerns.

Thanks for reading.

Advertisements
This entry was posted in SQL Server and tagged , , , , , . Bookmark the permalink.

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