Howdy people!
Ready for another post?
I'm sure someone will have reported this to you:
"Hey we have a problem! we have a database whose transaction file has grown and is now taking up several gigabytes!"
Today we will see why it happens
But i warn you! In order to understand completely the continuous you need to read this post: The WRITE AHEAD LOGGING mechanism
You need infact to learn how data are passed from the log to the data file.The post of today can be considered as a continuation of it.
Go!
Hey, the transaction log of my database is growing!
A little basic concept
Each database consists of two o more physical files.
- One or more files with mdf file extension are called data file because it contains data.
- One file with ldf file extension that's called transaction log file.
This transaction log file contain data not yet committed
For example, if start a transaction and then i run an insert, before the commit, data are stored in transaction log (ldf), after the commit data are persisted in the data file (mdf).
Simple and clear!
Of couse this is true even if i have an implicit transaction.
The recovery model
This is true if the recovery model is set to Simple.
Otherwise if the recovery model is set to Full then datas are not deleted from the transaction log until someone backup it.
Often this is the cause of a growing log! So check the recovery model!
Consider that if you create a new database then recovery model is set to full by default.
Other aspects
Even if your transactions are committed doesn't aspect to view your transaction log file become smaller. Surely a space inside the log file will be zeroed and marked as free but the transaction log file will not be shinked. Resize a file takes time and for this reason sql server does not shrink the file log when it has to enlarge it again a little later! The only way is that you shink manually the file.
Also remember that even the mere fact that the system is busy can cause the log to grow. This is absolutely normal.
So how we can remedy?
Check that all transactions are committed or rolled back
OK! That's all for for today! I hope you enjoyed this post, if so please show it to your friends and/or colleagues.
Luca
