MS SQL stores database changes into a log file that as more activity occurs on the database the larger the log file grows. When it comes to disk space limitations and performance this becomes a considerable problem. MS SQL Server provides a command to truncate the log file
DBCC SHRINKFILE(<log file>, 0, TRUNCATEONLY)
The command will shrink the log file specified. The log file name is meaningful only for a particular database. In order to run the command one needs to make sure that it is executed in the scope of the required database. To do this, MS SQL management console provides support to change the scope database through the command
USE [<database name>]
Another easier way is to change the database from the drop down list provided in the SQL Editor toolbar.
A problem that arises is that one needs to determine the name of the log file. The log file name can be retrieved from the database_files table in the sys tables and finding the record with the type description set to log.
DECLARE @log VARCHAR(200)
SELECT @log = [name] FROM sys.database_files WHERE type_desc='LOG'
Finally, combining this information into one script that truncates a database log file:
USE [<database name>]
DECLARE @log VARCHAR(200)
SELECT @log = [name] FROM sys.database_files WHERE type_desc='LOG'
DBCC SHRINKFILE (@log, 0, TRUNCATEONLY)
Another problem with the log file growth is related to the way it is set to grow. By default the growth of the log file is set to increase by 10%. As the log file increases in size the more space it requires to grow and as disk space is pre-allocated to improve write performance, the large space allocation becomes a problem. The table below gives an example of the growth rate of a 100MB file during the first 10 growths.
| Original Size | Growth Size | Final Size | |
| 1. | 100.00MB | 10.00MB | 110.00MB |
| 2. | 110.00MB | 11.00MB | 121.00MB |
| 3. | 121.00MB | 12.10MB | 133.10MB |
| 4. | 133.10MB | 13.31MB | 146.41MB |
| 5. | 146.41MB | 14.64MB | 161.05MB |
| 6. | 161.05MB | 16.10MB | 177.15MB |
| 7. | 177.15MB | 17.71MB | 194.86MB |
| 8. | 194.86MB | 19.48MB | 214.34MB |
| 9. | 214.34MB | 21.43MB | 235.77MB |
| 10. | 235.77MB | 23.57MB | 259.34MB |
From the table above it is clear that after 10 increments the log file has already exceeded 2.5times its original size. The more the file grows the bigger the increments will get.
Databases rarely need to write large portions of log file, except when a large amount of data is inserted or manipulated. To limit the growth of the log file without hindering much the performance it is best to grow the log file in well determined steps for example 10MB. To set this one can use the properties dialog in the SQL Server Management Console or alternatively through the SQL statements.
ALTER DATABASE [<database name>] MODIFY FILE (NAME = N'<database log name>', FILEGROWTH = 10240KB)
Conclusion
When dealing with large growth of the log file there are two things that need to be analysed:
- The growth rate of the log file
- Regular truncation of the log file, ideally after it has been backed up through the transaction log shipping process.