Expanding on the previous article, a fairly common problem we run into day to day is limited disk storage space. Whether for budget or infrastructure reasons, it is not always possible to increase the storage on our servers quickly, so implementing good practice and preventive action can save us the downtime of a database running out of space.
Here are three simple recommendations for controlling the rapid growth of your log files (*.ldf) in SQL Server:
a. Choose the right recovery model
In SQL Server a database can have one of three recovery models: Simple, Bulk-Logged and Full. This option determines how precisely a database can be restored. For example, having a Full recovery model is ideal for critical production environments because it lets us back up the transaction log, which we can then use to restore the database to a specific point in time. Bear in mind that if we choose this recovery model we need an automated transaction log backup process, otherwise the log will grow until it takes up all the space available.
On the other hand, if the database does not need a disaster recovery plan — as in test or development environments — it can be configured with a Simple recovery model, which will record a minimum of the transactions in the log and will also let us reclaim the space it occupies by shrinking the log file.
b. Compress the log file
In some cases the transaction log can be compressed because not all the space allocated on disk is being used. To check whether we can free space from the transaction log we can run the following command on the SQL instance: DBCC SQLPERF(LOGSPACE). Note the 'Log Space Used (%)' column: the lower the percentage, the more space we can free. If that is the case, we can shrink the relevant log file.
c. Move the log files elsewhere
According to SQL Server best practice, data files (.mdf) and log files (.ldf) should be stored on separate disks. That way we can stop unexpected log file growth eating into the space allocated for the data files. If that is not the case, we recommend relocating the data and log files respectively. It will require a restart of the SQL Server service for the changes to take effect.
In environments where we have enough storage disks, it is advisable to have one disk per database and per type of data file (.mdf and .ldf). That way we can isolate one database's space problems and prevent them affecting other databases on the same instance or server.
