I picked up the following from SQL Magazine. This code if run in the Query
Analyzer window will eventually shrink the log file. Make sure you are in
the right database before executing the statements. I've had to let mine
run for a minute or so, but if you monitor the logfile size eventually you
will see it shrink. For more details I would check SQLMag's
(www.sqlmag.com) website. They give a good explanation as to why this needs
to be done.
/* Do the following select by itself first.
You will need the file ID for the logfile that is
returned for the rest of the routine
*/
SELECT fileid, name, filename
FROM sysfiles
DBCC shrinkfile(<fileid>,notruncate)
DBCC shrinkfile(<fileid>,truncateonly)
CREATE TABLE t1 (char1 char(4000))
GO
DECLARE @i int
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 values ('a')
SELECT @i = @i + 1
END
TRUNCATE TABLE t1
backup log <db_name> with truncate_only
END
GO
______________________________________________________
Bill Grover
Supervisor MIS Phone: 301.424.3300 x3324
EU Services, Inc. FAX: 301.424.3696
649 North Horners Lane E-Mail: [EMAIL PROTECTED]
Rockville, MD 20850-1299 WWW: http://www.euservices.com
______________________________________________________
> -----Original Message-----
> From: Ian Tait [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, March 14, 2002 4:57 AM
> To: CF-Talk
> Subject: RE: Truncating Logs
>
>
> http://www.sqlserverfaq.com
>
> Has some useful info on this subject.
>
> One thing I did recently was to back up the database, detatch
> the db files
> using sp_detatch_db,
> and reattach just the data file using sp_attach_db, forcing
> it to create a
> new log file.
> This was for a log file that just would not shrink.
>
> No good if you want to keep the db on line though :-)
>
> HTH,
>
> Ian
>
> -----Original Message-----
> From: Duane Boudreau [mailto:[EMAIL PROTECTED]]
> Sent: 13 March 2002 21:06
> To: CF-Talk
> Subject: SQL: Truncating Logs
>
>
> Does anyone know the TSQL for truncating a transaction log?
> Mind sharing?
>
> TIA,
> Duane
>
>
>
>
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
