Trans Log not Truncating

Question

Post reply

Trans Log not Truncating

andyskinner

Ten Centuries

Points: 1256
More actions
October 22, 2002 at 2:19 am

#58133

Have a semi large database (about 15 gig data file) which generates quite a large Log file. I do transaction log backups twice a day but it seems quite a bit of space, about 7/8 gig won`t truncate from the log.
Is there any reason behind this, or any way to get rid of it, short of recreating the log file from scratch?
Andy.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

How are you backing up the transaction log?

Has it ever been small?

You can shrink the file, have a look at "shrinking transaction logs" in BOL to see a explanation of the log file

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Which version of SQL are you using. If 7 then you will need help killing the virtual logs inside the TL.

See http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1 for a great script to do this.

However I do suggest only do this when you are going to do a full backup afterwards.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

csraocvs SSC Journeyman Points: 94 More actions · Answer 3

If you do not care about the log data, do the steps in this order

1) "backup log" with truncate_only.

2) Then Shrink the log

An example below.

This will bring your logs to the original size when first created. I use these commands frequently and it works.

=====================================

use MY_DATABASE

go

backup log MY_DATABASE with TRUNCATE_ONLY

dbcc shrinkfile(2, 0)

go

======================================

-- Chandra Cheedella

andyskinner Ten Centuries Points: 1256 More actions · Answer 4

I think you misread my posting.

I said I backed up the logs twice a day, that has no effect. Have tried shrinking manually, has no effect either.

It`s all in 2000.

Andy.

madhu-a Grasshopper Points: 17 More actions · Answer 5

Did you try setting up the recovery model to simple under properties, options. Simple recovery model in 2000 is akin to truncate log on check point option in Sql 7.0.

timwilliams Ten Centuries Points: 1007 More actions · Answer 6

Andy

Have you tried the script above ...it does what it says on the tin...We have used it to great effect in similar situations

Tim