Clear logs:
Declare @ logicalfilename sysname,
@ Maxminutes int,
@ Newsize int
Use szwzcheck -- Name of the database to be operated
Select @ logicalfilename = 'szwzcheck _ log', -- Log File Name
@ Maxminutes = 10, -- limit on time allowed to wrap log.
@ Newsize = 20 -- 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
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
Replace szwzcheck with your database name and run it in the query analyzer.
A space with a full angle (for better display), you can change it yourself.