When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate automagically at some time point? Or do I have to truncate/shrink it manually?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
It will truncate automatically but that is very different to shrink. Truncation reclaims log space for re-use, shrinking physically reduces the file size to release space back to the OS. If your log has grown to its current size its likely that it will grow again if you shrink it.
I'd suggest getting a handle on what typical and maximum log usage is for your system. The query below (not mine, boosted from Glen Berrys DMV scripts) could be run manually or you could capture the output to a table via an agent job. If you log it to a table for a week or so you'll get a picture of typical usage and more importantly, when a process is causing the log to grow beyond what you expect.
Transaction Log Truncation describes both the when and why log truncation occurs.
Factors That Can Delay Log Truncation is a useful reference for understanding why your log may fail to truncate and therefore grow larger than expected.
No and no
If you shrink it, it will grow again and you'll have a fragmented file
As previously mentioned, no it will not automatically shrink itself. It will clean up some garbage however.
The reason being is that in the full recovery model you are telling SQL that you want to do tlog backups for point in time recovery, thus it keeps a record of all transactions made against a database.
Since you are telling it you want point in time recovery you need to do full backups and tlog backups. As you complete your tlog backups it will flush the contents of the log (besides the tail end) and start over.
It may help if you think of these files as containers.
My suggestion is that if the tlogs have become large and unmanageable, cut a full backup. Switch to the SIMPLE recover model and SHRINK the tlog file. Switch back to the full recovery model and perform fragmentation maintenance*. Like others have posted this is not the best of practices and will lead to high levels of fragmentation.
Plan and begin a backup regime after that.
* Index rebuild/reorganize operations and disk level defragmentation. This isn't a part of log maintenance: You maintain your T Logs by backing them up. They are containers that grow when they get close to capacity. Rebuild/reorganize can help recover from improper log management leading to large drive usage.