MSSQL Server 101

In the world of the Microsoft Sql Server a database consists (in the simplest setup) of two kind of files. The database files (*.mdf) and the logfiles (*.ldf). Without going into the details of the server one can say that the database file holds the information of the database and the logfiless tores the transactions that made up the data in the dabase. Whenever a transaction is started then the server starts to write the modifications into the logfile. As soon as the transaction is commited then the content is copied into the database file.

Lets bring another term into play: The recovery model. The recovery model of a database decides how detailed a database can be restored if it crashes for whatever reason. The simple recovery model is often used for single databases (often development databases) where point-in-time recovery or database mirroring is not an issue. Beside the fact that the logfile does not need to be truncated in this model it also provides the least protection from failures: Everything after the last backup of the database will be lost.

On the other end of the recovery model scale we have the full backup. It is the most versatile recovery model because if something crashes then every transaction up to a certain point in time can be restored from the logfile. The downside of this model is that it is the the database administrators responsibility to backup the logfile along the database file because otherwise it tends to grow until it consumes all the space of the partition where it is located. - I think that is what happened to you, because otherwise you would not be reading this article.

Truncate the log on MSSQL >= 2008

Once you have verified that the logfile is eating up all your memory you will discover (by googling and reading a lot of blogs) that you have to backup the logfile to be able to shrink it. So what if there is no space to backup the logfile ? ... Well then you can do the following trick:

This snipplet makes a backup of your logfile of the database "cms" but instead of writing it onto your disk it writes it to a special device 'nul'. This means that the backup of the logfile is stored nowhere and therefore consumes no space. In the last two steps the databasefile is shrinked to its minimum size and unused space within the datafiles is returned to the OS. Be patient these steps may take some time.

On earlier versions of the SQL Server the logfile could be backuped like this: BACKUP LOG Database WITH TRUNCATE_ONLY

What can we do that this does not occur again:

• On development servers it is often enough to set the recovery model to simple.
• Limit the max. size of your logfile.
• Make a directory with garbage data on your disk that you can delete in case of emergency. This will get you (depending on the workload of the DB) a few more minutes where you can truncate the logfile. Once the disk is completely full things tend to get more and more complicated. This is especially true if your database is located on youe C:\ partition (the partition where the OS is located)
• Place the logfile and the database files on different partitions - and if you want to optimize for performance these partitions should be on located different disks that are operated by different controllers.
• Alter your backup plan to backup the database AND the transaction log. Running a simple "Full Backup" will not remove inactive transactions (those are  the ones consuming your diskspace) from your logfile - You need to backup the logfile separately.
• If you are running bulk imports (or other high DB workload tasks) then it may be a good decision to set the recovery model to simple and when you are finished back to full.

This excellent german technet article about full tranaction logs describes the same problem.