Log Shrink Process

  • November 2019
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View Log Shrink Process as PDF for free.

More details

  • Words: 283
  • Pages: 2
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

Related Documents

Log Shrink Process
November 2019 2
Scientific Process Log
November 2019 1
Kokoda Journal Shrink
June 2020 0
Log
November 2019 0