Reduce Log file

Reduce Log file

Introduction

So there is a recurring question in the newsgroups and forums about the size of the file of transactions from a database of SQL Server. The problem is that the log file grows indefinitely until it is so big that eats the entire hard drive, arriving in the worst cases to fill with the resulting error:

Error: 1105, Severity: 17, State: 2 Could not allocate space for the transaction log for database 'MiBase' because the device is full. Dump the log or enlarge the device to create more space.

This error is not really a problem of SQL Server. Is normal on the server when we take into account in any database is to perform a series of maintenance tasks so that everything works properly. What we are signaling is that the log file can not grow, either because we have limited size, or because we have filled the entire disk, and if the server can not write to the log file can not continue working. But let us see a little how the data storage in SQL Server and that is the file of transactions (or log) to understand how we can resolve this error, and most importantly, how to avoid it.

File type

In any database SQL Server there are at least two files. One file is the database where the data will be stored in our tables (and other objects) and the other is the file of transactions. The transaction file is a series of records of all changes to the database and the transaction that has made each change. In the transaction log contains the start of each transaction. It also records changes to data and provides enough information to undo the modifications (if necessary later) made during each transaction. The file has extension mdf database while the transaction has ldf extension. In this case we will concentrate on the latter which is what can give us problems in terms of space.

Recovery mode

The recovery mode of the database is very important to understand where our problem arises. Depending on how we created the database several ways to recover the database in SQL Server. We have the full mode, the mass registration and simple. If you created the database did not specify any recovery mode chosen is full. To learn how you have configured one can look at the "Corporate Administrator" in the properties of the database in the "Options". Here we can see what the recovery mode of the database. We can also determine the recovery model by running sp_helpdb 'NombreBaseDatos' in Query Analyzer. If we do so in the results pane status where there is a column detailing, among other things, the model of recovery (Recovery = FULL).

Have full recovery mode means you can recover the database until the time when an error occurs or until a certain moment of time. Is the usual way in a production database.

In this recovery mode every transaction that occurs in the database (insert, modify, delete ...) was recorded in the transaction file (the. Log) so that it can reconstruct what happened to the database against time. But using this way means that the transaction file will grow indefinitely, even to be bigger than the database itself.

The solution to this growth is simple: to make backups. When we do a backup of transaction log data to pass to the backup disk is erased, leaving free space. So with every copy of the disc will remove the log file that is no longer necessary, leaving free space for recording new transactions that occur in our database.

The bad thing is that when this happens is released into space from log file, but it does not mean reducing the size of this file to disk. In this case we will have to shrink the log file in a second phase.

Operation of the log file

As they say what each BOL SQL Server transaction log file is divided logically into smaller segments called virtual log files (VLF). The virtual log files are the units of transaction log truncation. When a virtual log file no longer contains records for active transactions may truncated to ensure that there is space available for recording new transactions.

The minimum size of a virtual log file is 256 KB. The minimum size of a transaction log is 512 KB, which provides two virtual log files of 256 KB. The number and size of virtual log files in a file of transactions increases as does the log file. A log file can have a small number of small virtual log files while a log file can be larger virtual log files larger.

As we said when the transaction becomes very large the solution is to make a backup and that part of the file which makes a copy is released. That is, the VLF is emptied and once empty can be truncated to reduce the space occupied by the file on disk. To truncate the log file you can use Enterprise Manager using the "Shrink Database" or do it from Query Analyzer. I prefer Query Analyzer because it gives more of what we are doing and any errors that may appear. Let's see what we have to execute commands to do this from the Query Analyzer with TSQL.

Reduce the log file

Assuming we have a database name MiBase the steps would be:

1 - Open Query Analyzer. Execute the statement USE MiBase For the execution context is the database we want.

2 - Implement the decision CHECKPOINT To write to disk all the pages out of date (they are cached And are not yet on disk)

3 - Run the sentence EXEC sp_addumpdevice 'disk', 'CopiaMiBase', 'd: \ LogMiBase.bak' To create a hardware device to store the backup. This device is a file in d: LogMiBase.bak name. Make sure there is enough room for the backup. We can also create this device from the Corporate Administrator.

4 - Create a backup of the database MiBase BACKUP DATABASE TO CopiaMiBase This created a complete copy of the database

5 - Create a backup log file BACKUP LOG MiBase TO CopiaMiBase We have now created a copy of the log file information with the have released some of the VLF (virtual log file) Now if we look at the log file has been reduced, and if you have not Turn to the next point.

6 - Free space of the log file DBCC SHRINKFILE (MiBase_Log, 3000) This command frees space if there are any VLF gap. The first argument is the logical name of the file you want to reduce the size is 100 Goal Mb Although in this example we use size as 100Mb goal in each case will look what is the desired value and up depending on where you can get of VLF-free after you have the backup of the file Log. Once this is done and we should have a log file size reasonable.

Plan b

If we are interested not have backup of all data from the log file (or there is no space for the backup) we can follow a quicker method but does not back up this file, but that of the base Data: 1 - USE MiBase

2 - CHECKPOINT

3 - EXEC sp_addumpdevice 'disk', 'CopiaMiBase', 'd: \ LogMiBase.bak'

4 - BACKUP DATABASE MiBase TO CopiaMiBase

5 - BACKUP LOG MiBase WITH TRUNCATE_ONLY

6 - DBCC SHRINKFILE (MiBase_Log, 100)

This option simply truncates the log file the missing part of this file is not active.

A final solution

So that does not happen again is to have a maintenance plan for the base damage you make full backups and log file each time. How long is hard to say without knowing how to use the database and what is its size, but could be from several times a day to once a week. Of course it is much easier if we schedule the execution of work as the backup schedule and if you tell us some warning if you exceed the limit that we consider reasonable for the size of our files. Prevention is always better to correct the errors. Endnote I know it is not necessary to say, but if we do any task that can be dangerous is mandatory to backup the database and the database system if there are any unforeseen problems.