Hello all - I have a SQL Server 2000 database setup using the Full Recovery Model. Each night, we backup the entire database, and as such would like to truncate the log at this time as well.
Is the best way to do this to also backup the Transaction Log, and then perform a DBCC SHRINKFILE command? It just seems like there should be an easier way...?
Thanks!
Let's back up a few steps here.
What are you gaining by shrinking the transaction log every night?
The transaction log is designed to continually reuse its space internally. Whenever you back up the transaction log, all virtual log files (logical areas within the physical log file) which are not involved in any current transaction are marked as "free" and can be reused.
So, one way to manage the growth of a transaction log is to vary the frequency of log backups. The more frequent the log backup, the less data needs to be stored in the log (and the less data is lost in the event of a disaster).
Once the log reaches a steady-state, it shouldn't grow unless some exceptional update happens.
When you continually shrink and grow the file, you end up with filesystem fragmentation, which isn't good for performance. Also, the log file growth itself takes time.
No comments:
Post a Comment