3

I'm having this issues with too many logs files being generated. Over 60gb in the last 24 hours. It's not a massively high transactional website. What is the best way to read the log files and work out a likely cause. I'm using SQL Server 2008

1
  • What does your backup schedule look like?
    – Richard
    Aug 15, 2011 at 14:54

4 Answers 4

3

Usually, these questions arise from a misunderstanding of what the log file does. Usually, a larger than expected log is caused by a database in full recovery where log backups aren't being taken.

If you are taking log backups or your database is in simple recovery, there is probably a very large or long running transaction that has required 60GB of log space. KB317375 is a good starting point for understanding why unexpected log growth may occur and includes steps for identifying transactions that could be the cause.

2

In order to manage your database transaction log effectively you need to have at least a general understanding of how its content is being generated by your workload.

You need to find out what it is that has caused the 60GB of logged data in order to determine whether or not it is a valid outcome to be expected. Consider things such as long running queries, large data modifications and index maintenance operations as good starting points for your investigations.

Only once you understand your transaction log usage can you then confidently look to both size the transaction log accordingly and manage any log backup requirements dependant on recovery model.

0

Is there a lot of bulk inserted data into the database? There is always a rhyme and a reason for transaction log size.

0

Answering your question, you can access the log file through Management Studio. Under the server in Object Explorer, there is a "Management" folder. Expand that and you will see a "SQL Server Logs" folder. You can open the logs there.

Regarding the problem, KB873235 has some great tips.

Some snippets:

  • Shrink the transaction log file
  • Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
  • Change the recovery model.
  • Back up the transaction log files regularly to delete the inactive transactions in your >transaction log.
  • Design the transactions to be small. -Make sure that no uncommitted transactions continue to run for an indefinite time. -Schedule the Update Statistics option to occur daily.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.