Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Answers


SQL server has data files and log files, reason log files are increasing are a long running transaction running (Index maintenance)?, big transactions (i.e millions of transactions per hour)?, or your log file is  in default recovery mode(i.e full)?

How to stop growing log file too big

1.  Monitor default Size Limit:

In case SQL ldf file is growing too big then, put a large default size limit on the SQL server, so that it does not expands automatically and overloads the SQL server database.

2.  Using the Memory Units:

Remember to configure the expansion of log files by making use of the memory units instead of percentage if the SQL transaction log file grows quickly.

3.  Changing the recovery model:

 Simple Recovery model helps in controlling and shrinking the log file size. Based on how crucial the data is, user can choose any of the following recovery models namely,

a. Simple Recovery Model (recommended, take decision considering to DB uses, monitoring and backup)
b. Bulk-logged Recovery Model
c. Full Recovery Model (not recommended, take decision considering to DB uses, monitoring and backup)

Check following posts for detailed explanations,

https://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space

http://mahedee.net/how-to-stop-growing-log-file-too-big/

Share your answer


Share as a guest

Please subscribe me to the CodeChef4U.

By creating your new answer, you agree to the privacy policy and terms of service.