Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What does your backup schedule look like? –  Richard Aug 15 '11 at 14:54
add comment

4 Answers 4

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

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

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