LOG SHRINK PROCESS USE [pradeeptsingh] GO /****** Object: StoredProcedure [dbo].[LogShrink_Ajay] 10/17/2008 16:19:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[LogShrink_Pradeept]
Script Date:
( @logfilename varchar(200), @Newsize Int ) As SET NOCOUNT ON Declare @MaxMinutes Int,@LogicalFileName sysname Select @LogicalFileName = @LogFileName Declare @Originalsize Int Select @OriginalSize = size ----in 8k pages From sysfiles Where name = @LogicalFileName Select ' Original Size Of ' + db_name() + 'LOG IS ' + convert(varchar(30), @OriginalSize) + ' 8K pages or ' + convert(Varchar(30), (@OriginalSize*8/1024)) +'MB' from sysfiles where name = @logicalFileName IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Logtrunc_pradeept]') and OBJECTPROPERTY(id, N'IsTable') = 1) Drop Table Logtrunc_pradeept IF NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Logtrunc_pradeept]') and OBJECTPROPERTY(id, N'IsTable') = 1) Create Table Logtrunc_pradeept ( dummycol char (7000) not null ) Declare @Counter Int, @StartTime datetime, @Trunclog varchar (255) Select @StartTime = Getdate(), @Trunclog = ' Backup Log with Truncate_Only' DBCC shrinkFile ( @LogicalFileName, @NewSize )
['+ db_name() + ']
Exec (@Trunclog) While @MaxMinutes > Datediff(m, @Starttime, Getdate()) --- time has not expired ANd @OriginalSize = (Select size From sysfiles Where name = @LogicalFileName) --Log Not Shrinked AND (@OriginalSize * 8/1024) > @NewSize ---- The Value passed in for new size is smaller then the current size BEGIN Select @Counter = 0 While ((@Counter < @OriginalSize / 16 ) ANd (@Counter < 50000)) BEGIN Insert Logtrunc_pradeept values ('Fill Log') Delete Logtrunc_pradeept Select @counter = @counter + 1 End Exec (@Trunclog) End SELECT 'Final Size OF ' + db_name() + ' LOG IS ' + CONVERT(VARCHAR(30),size) + ' 8K pages OR ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles Where name = @LogicalFileName Print '***Execute DB Maintenance Task***' Set Nocount off