|
What on earth is an MSSQL Log and why is mine so BIG? |
 |
 |
 |
A very common problem people have when running MSSQL databases is that the log file gets enormous and nobody can tell why. How is it that a database with a 1GB data file can have a log file in excess of 10 or 20GB? It makes no sense!
I have been asked this question a number of times and I thought a little background to the purpose of the log file will explain this phenomenon. I have also given a couple of tips on how to manage the log file size.
Every database has at least one data file and one log file. Needless to say the data file is where the data is kept and is therefore the more important of the two files in most cases. Transaction logging is considered an essential part of ensuring data integrity and is therefore compulsory (you simply can’t switch it off).
The log file keeps a record of every single change made to the data in the data file. The reason it does this is primarily threefold:
- Transaction Logging - While a transaction is being written to the database the log file keeps track of what is doing so that if the transaction fails it can roll-back what it has done so far and restore the database to the situation immediately before the transaction took place. This is so that you never get “partial updates” or incomplete transactions in the database.
- Incremental Backups - Because the log file stores every change to the database it is actually only necessary to back up the log file since the last full data file backup because you can recreate the data by taking the last full data backup and applying the transactions again one at a time.
- Point in time restore - It follows from the above that in the same way you can recreate the current situation by reapplying all the transactions up to the current point in time you are also able to restore your database as at any point in time in the past. You simply apply only the transactions up to that point.
The log file is also essential for transactional replication and standby server solutions but that’s a completely separate discussion.
The log file is therefore very important and is also likely to be quite big if you don’t truncate some of its contents from time to time and the log can only be truncated once it has been backed up.
It is important to note that a transaction log backup is not the same thing as a database backup. It is quite possible to back up the database without backing up the log file. This is normally where people come unstuck because they religiously perform database backups but never perform log backups. As a result the log file just grows and grows (until it runs out of space). I have seen log files that are more than 20 times the size of the data file!
So how do I keep the log file to a manageable size?
1. Perform regular transaction log backups and resize the log file.
The best way to keep the log file manageable is to do regular transaction log backups and once you have backed up the log file onto another drive or onto tape etc. You can then truncate (clear down) the log file because you can always get it back if you need it. As long as the log file is available somewhere it doesn’t need to clog up your active drives.
The transaction log backups are not enormously complex to set up or run but it might be a good idea to involve a DBA to at least set it up initially and explain what you need to do on a regular basis. It is also a very good idea to have a qualified DBA have a look at the system reasonably regularly to ensure everything is running properly and that there are no impending disasters.
2. Switch over to the Simple Recovery Method
An alternative solution is to use the “simple recovery model” which basically sets the database to only log the transactions until they reach the next database checkpoint or simply until the data is committed.
Under the simple recovery model it is only possible to restore the data up to the last full backup. Changes since the last full backup are not logged at all so point-in-time restores are not possible.
On the plus side the log file will never grow larger than your uncommitted transactions at any point so you won’t run into disk space issues because of the log file again.
The question to ask yourself is whether you are willing to lose the data captured since the last data backup? If you are running backups nightly you could lose up to 1 day’s work (provided the backup was successful).
Realistically most organizations generally assume that last night’s backup is the best they can do anyway and have designed their DR strategy around this so perhaps the simple recovery model is worth considering but only if you fully understand the implications.
|
|
Disaster Recovery – obscure mystery or just plain sense? |
|
|
|
Recently more than one of our clients needed to recover from data disasters of varying magnitudes. The recovery in all cases amounted to more of a rebuilding than a recovery because the DR strategies in place simply didn’t achieve what they were intended to.
We live in a new world where a large proportion of a company’s wealth is held in bits and bytes on some form of digital media. It’s convenient, it’s fast, it takes up practically no space, and it’s as volatile as Ether.
Anybody who has ever had a hard disk crash or a PC stolen will tell you just how vulnerable your data is when it is held on any digital media. Fortunately we all realize this and we’ve all implemented thorough disaster recovery strategies to protect us from any possible data loss… or have we?
|
|
Read more...
|
|
|
|
|
Blog
