SQL 2005 Log file management

Question

Dear All,

Am not a expert in SQL, so wanted your help in understanding how to manage the SQL log file. We have around 900 nodes in the Orion NPM and its been running fine since 3-4 months. Though the database size is around 470Mbps but the log file has gone beyond 40GB.

Kindly advice as to if this is OK or some error is causing such high log file & is there a way to compress this log files.

Thanks & Regards

Vinod Dhure.

Yann · Answer

Hi Vinod,

The following thread might help you too:

~Yann

davbyrd · Answer

Vinod,

First I recommend getting a DBA to review your situation. If you do not have a DBA then the directions below should help.

.LDF file grows out of control:
1.) Make sure recovery model is set for simple and not full
2.) Shrink the database
3.) Backup the database, this will mark the data in the transaction logs as committed and no longer necessary.
4.) Shrink the database again to reclaim the log space.

Hope this helps,
David