I'm somewhat of an accidental DBA, being a developer who inherited a couple of database servers (2005 and 2008) from someone who knew little about database administration, and seemingly had even less interest in learning more about the subject.
I'm learning as I go, and am currently trying to figure out transaction log files.
All our databases have been set up with the simple recovery model and autoshrink. I've understood that using autoshrink is usually a horrible idea, but it's my understanding this was done in order to stop the transaction logs from growing out of control. (Does autoshrink actually shrink the log file(s) or just the DB?)
I found this about SQL Server 2012, and was wondering if it's true about 2005 and/or 2008, and exactly what it means: "When a database uses the simple recovery model, the Database Engine truncates the transaction log after a checkpoint. […] The Database Engine triggers an automatic checkpoint under the simple recovery model when the virtual log becomes 70 percent full." Where is the virtual log size specified?
I want to disable auto shrink on all databases, but before I do that, I need to know that the log files won't grow out of control quickly.
Any help would be greatly appreciated.
Best Answer
A single transaction log file has both a physical size (that you see on disk), and it's also broken down within the physical file into logical sections called virtual log files (VLFs).
Both auto-growth and auto-shrink operate on the physical transaction log file.
Transaction log truncation (also called "log clearing") operates on the logical sections of the transaction log (VLFs), and does not affect the physical file size. This part is frequently the subject of confusion.
A log file must always grow to accomodate a large transaction; turning off auto-shrink will leave the log file with its maximum needed size, instead of physically decreasing its size.
If you don't have large transactions, it will be safe to disable auto-shrink; the log files will not grow without bound like would happen if the database was in
FULLorBULK_LOGGEDand you weren't taking transaction log backups.This behaviour is the same for SQL Server 2005+.