SQL Log Files Too Large – How To Shrink Log File In SQL Server

Sometimes SQL’s log files can grow to a very large size and you need to shrink the log files before you run out of disk space. This article doesn’t go into a great deal of depth about log files, how they work, backups and the best way of handling log files, but simply gives you the basics and tells you how to shrink log files that are too large.

SQL keeps a log file for each database that contains a list of SQL transactions that occured after the last time the log files were backed up or truncated (the process of shrinking log files). These are an essential part of the backup if you are doing a Full or Bulk-Logged backup and not really used if you’re operating a Simple recovery model backup. If you’re doing a full backup, the logs allow you to recover SQL to any point in time, replaying the logs after the full backup up to the point you want to restore to.

The SQL log files for each database grow larger and larger as time passes and are only truncated when they’re backed up or a script is run against the database. Here is a script to shrink large log files in SQL Server:

Use DatabaseName
GO
Alter Database DatabaseName Set Recovery Simple
GO
Alter Database DatabaseName Set Recovery Full
GO
DBCC SHRINKFILE (‘DatabaseLogName‘, 1)
GO

This script puts the database into Simple recovery mode, then back to Full recovery mode then shrinks the log file. You can get the DatabaseLogName by right clicking the database in SQL Management Studio and clicking Properties then the Files tab. The name of the database will be listed in the table under Logical Name, where the File Type is Log.

Addendum: You can also shrink large SQL log files in the GUI by opening SQL Management Studio, right clicking on the database, clicking Tasks, Shrink, Files and selecting Log as the File Type then clicking OK.

VN:F [1.9.22_1171]
Rating: 8.0/10 (1 vote cast)
SQL Log Files Too Large - How To Shrink Log File In SQL Server, 8.0 out of 10 based on 1 rating

Leave a comment

Your email address will not be published. Required fields are marked *