Here's a script i use, contains some of each.

/********************************************************************/
/* This script shrinks log files. It must be run at least twice. */
/* For a discussion of the how and why, please see SQL Server */
/* "Books on Line" topic ===>>> DBCC SHRINKFILE */
/* */
/* to use: */
/* Change "YOURDB" to your database name, and verify */
/* the logfile's FILE name (not the Location). */
/* */
/* */
/* To locate the FILE name right-click your database icon. Select */
/* properties and navigate to the log file information. */
/* Pick the FILE NAME **not** the Location of file. */
/* */
/* When you run this command for the first time, you will see output*/
/* which will look similar to this: */
/* */
/* DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages */
/* ------ ------ ----------- ----------- ----------- -------------- */
/* 10 2 385280 1280 385280 1280 */
/*(1 row(s) affected) */
/* */
/* the second (and third or fourth) iteration of the script will */
/* cause the CurrentSize value to go down to the MinimumSize value. */
/* When this happens you will have completed the log file downsize */
/* */
/*DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages */
/* ------ ------ ----------- ----------- ----------- -------------- */
/* 10 2 937 937 936 936 */
/********************************************************************/

use YOURDB -- <<<< change this
GO
--select * from sysfiles

DECLARE @DB VARCHAR(40)
DECLARE @LOGFILE VARCHAR(40)
SELECT @DB='YOURDB' -- <<<< change this

SELECT @LOGFILE='xyz_log' -- from Enterprise Manager/Properties,
or . . .
select @LOGFILE = (select name from sysfiles where name like '%log%')
print 'We think the logfile is '+@LOGFILE

CHECKPOINT

EXEC('DBCC SHRINKFILE (' + @LOGFILE +', 1, NOTRUNCATE)')
EXEC('DBCC SHRINKFILE (' + @LOGFILE +', 1, TRUNCATEONLY)')

CREATE TABLE t1shrink (char1 char(4000))
DECLARE @i int SELECT @i = 0 WHILE (@i < 300) begin INSERT t1shrink values
('a') SELECT @i = @i +1 END DROP TABLE t1shrink

EXEC('BACKUP LOG '+@DB+' WITH TRUNCATE_ONLY')
EXEC sp_spaceused @updateusage='true'


-rjm

----- Original Message -----
From: "Chris Kempster" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Thursday, March 25, 2004 8:12 AM
Subject: RE: Is there a way to remove/truncate the log file in sql 2000?


> I believe you are after dbcc shrinkfile
> You may find the backup log option truncates the logical log files, but
the
> physical file remains large, which I believe is your issue? Be aware that
> shrinking will only go down to the last active logical log file within the
> transaction log, so a 8gb file for example may only shrink to 7gb if the
> active portion is at this back end of the file.
>
> Cheers
>
> Ck
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Get today's cartoon: http://www.LazyDBA.com
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page