Script: Reduce Log File depending on DB Data Files size

For Spanish version visit this link Script: Reducir Log porcentualmente dependiendo del tamaño de los ficheros de Datos
 
A friend asked me about a dynamic script to reduce the DB Log File based on the total size of DB Data Files, here is the answer:
/*--------------------------------------------------------------------------------------
-- File: ShrinkToPercent.sql
-- Author: Fran Lens (http:\\www.lensql.net)
-- Date: 2012-03-27
-- Description: Reduce LogFile Size based on percentage of the Total Size of DataFiles
--------------------------------------------------------------------------------------*/
DECLARE @SelectDB varchar(50)
DECLARE @ShrinkPercent float
DECLARE @DBid int
DECLARE @RecoverySimple varchar(200)
DECLARE @RecoveryFull varchar(200)
DECLARE @ShrinkCommand nvarchar(200)
DECLARE @ShrinkFile varchar(50)
DECLARE @ShrinkValue varchar(50)

SET @SelectDB = 'AdventureWorks2008R2' -- Database whose Log will be Reduced
SET @ShrinkPercent = 30 -- Percentage of the DataFiles Size to Reduce the LogFile
						-- Example: With a value of 20 Percent and 1GB of Datafiles Size, the Log will be reduced to 200MB

SET @DbId= (select database_id from sys.databases where name=@SelectDB)
SET @RecoverySimple = 'ALTER DATABASE [' + @SelectDB + '] SET RECOVERY SIMPLE WITH NO_WAIT' -- Change the recovery model to Simple
SET @ShrinkFile = (SELECT name from sys.master_files WHERE database_id = @DBid and type_desc = 'LOG')
SET @ShrinkValue = (@ShrinkPercent)/100 * (SELECT SUM(size)/128 FROM sys.master_files WHERE database_id = @DBid and type_desc = 'ROWS')
SET @ShrinkCommand = 'USE [' + @SelectDB + ']' + CHAR(13)+ 'DBCC SHRINKFILE('+ @ShrinkFile+',' + @ShrinkValue + ')' -- Reduce the LogFile Size
SET @RecoveryFull = 'ALTER DATABASE [' + @SelectDB + '] SET RECOVERY FULL WITH NO_WAIT' -- Change the recovery model to Full

EXEC (@RecoverySimple)
EXEC sp_executesql @ShrinkCommand
EXEC (@RecoveryFull)

About Fran Lens
SQL Server Analyst at Avanade Spain http://www.lensql.net

One Response to Script: Reduce Log File depending on DB Data Files size

  1. Pingback: Script: Reducir Log porcentualmente dependiendo del tamaño de los ficheros de Datos « LenSQL

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

%d bloggers like this: