Script: Reduce Log File depending on DB Data Files size
2012/03/30 1 Comment
/*-------------------------------------------------------------------------------------- -- 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)
Advertisements