Shrink Database Log Files

If the log file in the SQL database is too large, you can try the following code to narrow down the log file to the desired size. Copy the code to the query analyzer, modify the three parameters (Database Name, log file name, and target log file size), and run the command.

Set nocount on
Declare @ logicalfilename sysname,
@ Maxminutes int,
@ Newsize int

Use mydb -- Name of the database to be operated
Select @ logicalfilename = 'mydb _ log', -- Log File Name
@ Maxminutes = 10, -- limit on time allowed to wrap log.
@ Newsize = 15 -- the size of the log file you want to set (m)

-- Setup/initialize
Declare @ originalsize int
Select @ originalsize = size
From sysfiles
Where name = @ logicalfilename
Select 'original size of '+ 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 of '+ 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