SQL Server LDf files growing to big

[gavinjb]

Hi,

I have a couple of DB's which have a lot of transactions against them overnight and the log files grow to hugh sizes, and I have to compact these files down several times a week. What is strange though is I have anoth DB, which as even more transactions against it and this db, doesn't have the problem.

Does anyone know how I can auto compact these files on a regular basis?


Thanks,


Gavin,

 

[SQL_Hell]

Hi

Do you do any transaction log backups on this server? if not you get set th recovery mode to 'simple' this should help the database log not grow so much.

to truncate the log

you fistly try running a backup, see if that shrinks it a bit
if that doesn't work, run the following (changing mydatabase for your database name)

backup log mydatabase with truncate_only

and if that doesn't work try this (changing mydat_log for your logfilename)

dbcc shrinkfile ('mydat_log',1)

 

[gavinjb]

Hi,

Thanks for the help, I curreently do run the Database Log Bakcup and ddbc on a regular basis, but I am looking for a way to remove the need to do this, (hopefully by creating a job, or changing a setting on the db).

 

[SQL_Hell]

have you tried the simple recovery mode?

 

[gavinjb]

Ni, I had it on full, I have changed it to simple, I will see if this makes any difference

 

[pdx_man]

Before changing the recovery mode to simple, be sure that you have a reliable method of tape backup of the data and ensure that you will not have issues if you have a disaster. Refer to BOL and Internet searches to see what the best recovery mode is for your environment.

Here, after I have verified a good backup, I change the mode to simple, do what SQL_Hell said above, then change back to full.