Paste the following code into SQL query analyzer (query analyzer) and execute it (note that you can modify the three parameters in the red part ):
Set Nocount On
Declare @ Logicalfilename sysname,
@ Maxminutes Int ,
@ Newsize Int
Use Dicky -- Name of the database whose logs need to be reduced
Select @ Logicalfilename = ' Dicky_log ' , -- The logical name of the log file. It is not a physical file name.
@ Maxminutes = 10 , -- Limit on time allowed to wrap log.
@ Newsize = 2 -- The size of the log file to be reset (unit: m). The value must be smaller than the size of the original file.
-- Setup/initialize
Declare @ Originalsize Int
Select @ Originalsize = Size
From Sysfiles
Where Name = @ Logicalfilename
Select ' Original size ' + Db_name () + ' Log is ' +
Convert ( Varchar ( 30 ), @ Originalsize) + ' 8 K pages or ' +
Convert ( Varchar ( 30 ), (@ Originalsize * 8 / 1024 )) + ' MB '
From Sysfiles
Where Name = @ Logicalfilename
Create Table Dummytrans
(Dummycolumn Char ( 8000 ) Not Null )
Declare @ Counter Int ,
@ Starttime Datetime ,
@ Trunclog Varchar ( 255 )
Select @ Starttime = Getdate (),
@ Trunclog = ' Backup log ' + Db_name () + ' With truncate_only '
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)
And (@ Originalsize * 8 / 1024 ) > @ Newsize
Begin -- Outer Loop.
Select @ Counter = 0
While (@ Counter < @ Originalsize / 16 ) And (@ Counter < 50000 ))
Begin -- Update
Insert Dummytrans Values ( ' Fill log ' )
Delete Dummytrans
Select @ Counter = @ Counter + 1
End
Exec (@ Trunclog)
End
Select ' Final size ' + Db_name () + ' Log is ' +
Convert ( Varchar ( 30 ), Size) + ' 8 K pages or ' +
Convert ( Varchar ( 30 ), (Size * 8 / 1024 )) + ' MB '
From Sysfiles
Where Name = @ Logicalfilename
Drop Table Dummytrans
Set Nocount Off
