TRUNCATE TRANSACTION LOG URGENT - LOGS OVER 40Gb ON 60Gb drive 1

[osjohnm]

Hi

I have gone through some of the previous threads about transaction logs. I have gone through BOL and MSDN.
I am still confused

I have tried DBCC ShrinkFile and sp_dboption for the truncate with checkpoint.

I still have 45gbs of transaction log on our dev server which has a single 60gb drive. i run full backups on all databases every night but I don't backup the transaction logs.

how do i get rid of the transaction logs in the shortest possible time?

once i have set truncate with check point to true what must i then do?

the server runs sql 2000.

any help is greatly appreciated as the server is running out of space.

thanks
john

 

[tlbroadbent]

If you are truncating the logs on checkpoint then run DBCC SHRINKFILE(2,0) in every database that you want to shrink the log file. HAve you read the FAQS in this forum about shrinking databases and logs. Read faq183-1534 if you haven't already done so. There is a reference to another quick method of shrinking the log which involves detaching the database. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[osjohnm]

I have found the guilty culprit. One particular log is 43.5gb in size(on a database that is old an never used).
I had tried BACKUP LOG WITH TRUNCATE on another smaller log file(91Mb) which worked. BACKUP LOG WITH TRUNCATE on the 44gb had no effect. setting truncate log on checkpoint to true and running DBCC SHRINKFILE(2,0)
worked like a bomb!

Would it be advisable to set truncate to true on all the databases?

If so would it be a good idea to setup a job that runs once a month that executes DBCC SHRINKFILE (2,0) on all the databases?

Thanks 4 your speedy and helpful response Terry

John

 

[tlbroadbent]

Great. Glad you solved the problem. I repeat a caution I've given many times. It is not good policy to Truncate Log on Checkpoint or Backup With Truncate_Only in a production environment unless the data in the database is static or can be recovered easily from other sources. Truncating the log eliminates the possibility of recovering transactions. You should do an immediate full backup whenever the log is truncated. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[eric81]

You also want to verify your database recovery model to see if its' set for Simple or Full, if it's set to Full you will generate more data to your transaction log.

 

[tlbroadbent]

Recovery models FULL, BULK_LOGGED and SIMPLE only apply in SQL 2000 not SQL 7. Normally, on a production database you want to set recovery to FULL. It provides the greatest recoverability of data.

The SIMPLE recovery model provides no transaction log recovery. The SIMPLE revovery model in SQL 2000 is equivalent to Trunc. Log On Checkpoint in SQL 7. Thus in SQL 7 you don't set Trunc. Log on Checkpoint if you want maximum recoverability. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[osjohnm]

Since SIMPLE RECOVERY mode in 2000 is the same as setting truncate log on checkpoint to true in 7.
What will cause the transaction log to get smaller without any intervention once the recovery mode has been changed to simple?

 

[tlbroadbent]

Nothing will automatically cause it to get smaller. Truncating the log on checkpoint merely removes committed transactions from the log. This usually helps keep the log from growing very large though it can still grow. You still need to shrink the file if it has grown for some reason. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.