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 have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is no longer available with 2008 and later databases.

I do have a script that truncates the log file:

USE [MyDatabase]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC shrinkfile('MyDatabase_log', 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

This truncates the log file completely, but my question is: Does this affect performance?

I perform two Full backups daily so the log should not really be necessary as far as data roll-forward is concerned.

share|improve this question
add comment

migrated from stackoverflow.com Jul 12 '11 at 15:56

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 20 down vote accepted

I really recommend reading Importance of proper transaction log size management by Paul S. Randal.

The quintessence is that there's only two really good ways to do transaction log handling:

  1. Either go with regular LOG file backups and the LOG-file will reuse it's space after each LOG backup and won't grow indefinitely, or

  2. Use SIMPLE recovery model and don't have to care about your LOG-file size, as you do regular full backups.

What concerns LOG-file truncation and performance is that you will always get a performance hit when the LOG file is to be increased (a quote from the above linked blog post):

If you shrink the log, then it's going to grow again - possibly causing VLF fragmentation, and definitely causing your workload to pause while the log grows, as the log can't use instant initialization [...]

Update: Don't mistake LOG file truncation for DATA file shrinking. DATA file shrinking is really bad. See Why you should not shrink your data files for details.

share|improve this answer
2  
Upvoted because EVERYONE who manages a SQL Server database NEEDS to read those posts and understand why shrinking databases makes God kill kittens. –  SQLChicken Jul 14 '11 at 17:35
add comment

OK first yes the log is necessary even with the daily full backups if you want to recovoer in the event of a problem. We backup our transactionlog every 15 minutes. The problem is that you are not backing up your transaction log and that is why the log grows so outrageously. You should almost never need to shrink a transaction log if you are doing correct transactionlog backups.

You will need to back up the database before you truncate the log. I suggest doing it on off hours so no new data is inserted between the backup and the truncation. Then set up proper transaction log backups so that you never have this problem again.

As to affecting performance, well without knowing the details of your system hardware and usage, that would be hard to say.

share|improve this answer
add comment

How quick does the transaction log grow? If it is rather rapid, you will affect performance by shrinking it down to near nothing, as it has to spend time growing it back. This does not mean you should not shrink it from time to time, but you have to think about the size issue rather than just shrink down to minimum. Is the perf hit huge? Probably not, but it depends on load on the server (number of transactions, etc).

One thing I find problematic is "I perform 2 Full backups daily so the log should not really be necessary as far as data roll-forward is concerned." The log is extremely important for points between your full backups. Even twice a day does not eliminate the need for a log file for disaster recovery, unless this is a read only database (if it was, you would not see the huge increase in log file, however).

share|improve this answer
    
The log takes about 4-6 months to get to this size, so it's not very rapid. I thought (maybe incorrectly) that the log file held transactions between full backups, that's why I mentioned that I do 2 full backups a day so the content of the log between can't be too great. As I said, I may have misunderstood the log file concept. –  MartinHT Jul 12 '11 at 14:50
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.