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
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.
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.
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.
- 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.