How to Shrink MSSQL database log files?

[Traged1]

Customers are complaining that their MSSQL log files are well over 100MB, why is this happening and how can I fix/shrink it safely?

 

[jpboyce]

Logs should be truncated whenever you perform a full backup of the database. You can also manually truncate them. A script I found to do it a long time ago:

declare @dbname nvarchar(255)
set @dbname = 'yourdatabasenamehere'
backup log @dbname with truncate_only
DBCC SHRINKDATABASE (@dbname, 0)

 

[Traged1]

Thanks I have tried doing a full backup of this database and it did not truncate the log file. How do I execute the above script? Do I save it as some kind of file and then execute it? If so what type of file should I name it?

 

[jpboyce]

If you run the code in Query Analyser, it should work. Strange that your database logs aren't being truncated during a backup.

 

[Traged1]

I know I did it with Enterprise manager right on the server itself, and I made sure that the Option to truncate inactive log etnries was checked.

 

[Traged1]

Yep, that worked, thanks. I certainly hope that I don't have to do this manually all the time for each of our 1000+ customers using PLESk for windows.

 

[adrianodalpra]

Another solution to avoid database log files grow in time is change the database recovery model to SIMPLE.

This will truncate log at each checkpoint.

 

[Traged1]

Yes it appears that the recovery model on the troubled server are all set to full and not Simple, I switched them to simple recover model to solve the problem. Thanks.

 

[Traged1]

What I do not understand is why this server is still setting up the databases using the FULL recover model, yet on a new server with PLESK 8.1 same as this server, it sets up the databases using the simple recover model? Also why is the auto close & auto shrink option not being set?