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.

Due to the size of transaction log file (it grew up to 200 GB) I had to delete it because the disk was running out of space. I know this is not a good practice but anyway after that the performance is very bad.

Does anyone know whether it is possible to improve performace and how in this situation?

share|improve this question
1  
did you delete the log files from Data directory? what kind of performance issues are you facing? did you ran profiler? –  Coder Hawk Jan 10 '11 at 11:21
add comment

2 Answers

up vote 16 down vote accepted

The problem probably occurred because in order to delete the transaction log you would have had to stop the SQL Server. This causes the buffer pool (cached data and execution plans) to be erased - nothing can be in memory for a process that isn't running anymore. Now that you've restarted your server, all of your execution plans need to be recompiled.

I would also guess that you're running the server with the default transaction log settings wich specify a default size of 1MB for the transaction log and an automatic growth of 10%. These are very bad settings for performance. The transaction log has to 0 out the file when it grows. You can read more about transaction log management in 8 Steps to Better Transaction Log Management and Database Maintenance Best Practices Part III - Transaction Log Maintenance.

The first question is what did you do to the transaction log? You should never delete a transaction log. This is how your database is able to recover from a crash.

The second question is do you have a log backup solution in place? That is the only reason your transaction logs are growing so large. I suggest looking at Ola Hallengren's excellent backup scripts.

Typically, you want to grow your transaction log to a fixed, sane, size to start with and leave it there. This will depend on your database, but in the past I've monitored log file growth over the course of the week with full database backups happening every morning and log file backups running every 15 minutes. Once I've figured out how large the transaction log can get, I size the transaction log to about 15% larger than that (but as a multiple of 8GB).

share|improve this answer
    
nice answer and excellent links! –  Coder Hawk Jan 10 '11 at 12:37
add comment

There are two ways to prevent a database's transaction logs from growing without end:

  1. Backup the transaction log, or
  2. Set the database recovery mode to SIMPLE.

Many people think that doing a full database backup will truncate the log - it does not! Only a log backup will truncate the log.

Setting the recovery mode to SIMPLE (database properties page in SSMS) will truncate the log regularly (every time a checkpoint occurs), but you lose the ability to do incremental backups (ie. transaction log backups). So you can only recover your data to the last full backup.

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.