SQL Server clears the log Stored Procedure procedure of cleaning SQL Server's log

 

Create   Procedure   [ DBO ] . [ Strink_logspace ]
  As
Set Nocount On
Declare   @ Logicalfilename Sysname,
@ Maxminutes   Int ,
@ Newsize   Int

Select @ Logicalfilename   =   Rtrim (Name ),
@ Maxminutes   =   10 , -- Maximum execution time
@ Newsize =   10 -- Minimum space
From Sysfiles Where Status &   0x40   =   0x40

-- Setup/initialize
Declare   @ Originalsize   Int
Select   @ Originalsize   = Size -- In 8 K pages
From Sysfiles
Where Name =   @ Logicalfilename

Select   Db_name () + ' Original Log Size '   +
Convert ( Varchar ( 30 ), @ Originalsize ) +   ' Pages/8 K or '   +  
Convert ( Varchar ( 30 ),( @ Originalsize * 8 / 1024 )) +   ' MB '
From Sysfiles
Where Name =   @ Logicalfilename

Create TableDummytrans
(DummycolumnChar(8000)Not Null)

-- Wrap log and truncate it.
Declare   @ Counter Int ,
@ Starttime   Datetime ,
@ Trunclog Varchar ( 255 )
Select @ Starttime   =   Getdate (),
@ Trunclog   =   ' Backup log [ ' +   Db_name () +   ' ] With truncate_only '
-- Try an initial shrink.
DBCC Shrinkfile ( @ Logicalfilename , @ Newsize )

Exec(@ Trunclog)

-- Wrap the log if necessary.
While @ Maxminutes   >   Datediff (MI, @ Starttime , Getdate ()) -- Time has not expired
And   @ Originalsize   = ( Select Size From Sysfiles Where Name =   @ Logicalfilename ) -- The log has not shrunk
And ( @ Originalsize   *   8   / 1024 ) >   @ Newsize -- The value passed in for new size is smaller than the current size.
Begin   -- Outer Loop.
Select   @ Counter   =   0
While (( @ Counter   <   @ Originalsize   /   16 ) And ( @ Counter   <   50000 ))
Begin   -- Update
Insert Dummytrans Values ( ' Fill log ' ) -- Because it is a char field it inserts 8000 bytes.
Delete Dummytrans
Select   @ Counter   =   @ Counter   +   1
End -- Update
Exec ( @ Trunclog ) -- See if a trunc of the log shrinks it.
End -- Outer Loop

DBCC Shrinkfile ( @ Logicalfilename , @ Newsize )

Select   Db_name () + ' Last Log Size '   +  
Convert ( Varchar ( 30 ), Size) +   ' Pages/8 K or '   +  
Convert ( Varchar ( 30 ), (Size * 8 / 1024 )) +   ' MB '
From Sysfiles
Where Name =   @ Logicalfilename
Drop   Table Dummytrans
Print   ' * ** Database logs are compressed successfully *** '