6 Key Steps to Follow when Shrinking a SQL Server Log File

In this article we look at the process of shrinking transaction log files in Ms SQL Server

SQL Transaction Log FilesSQL Server allows you to shrink log files to effectively free up space in quick time. It might happen that your log files do not make use of the entire space available in the transaction log files, to make full use of the available space, you can opt for shrinking the log files. Usually you can shrink your log file only when your database is online, or a single, virtual log file is not in use. Sometimes you may not able to shrink a log file till, after the truncation of the next log. The process of shrinking log files involves occupying unoccupied spaces in the front of file by moving pages located in the end of file, thus recovering space.

Things to Keep in Mind while Shrinking Transaction Log Files

Shrink A Log FileShrinking the transaction log file is one of the best and most preferred way of making full use of the unused spaces, however, the process is not as simple as it sounds and a few key points need to be considered before implementing it.

  1. One of the greatest limitation of the process is that, it cannot be used to make a primary data file concise than the given size of the primary file present in the model database.
  2. When one shrinks a file, the data can fit into any available space, thus becoming scattered and causing index fragmentation. This will not only slow down your overall operations but also make things more confusing. To avoid fragmentation, it is recommended to rebuild indexes after shrinking the log file.
  3. To be able to use the feature, you will require membership in the relevant sysadmin fixed server role or alternately in the db_owner fixed database role. These are the necessary permissions.

Shrinking the transaction log in SQL Server can be of great use if implemented properly, while implementing any kind of feature that make changes in the database, the DBA should be extra careful and keep all the important points in mind.

Steps for Shrinking the Transaction Log File

SQL Server allows you to shrink log files in two different ways, either by making use of the Transact SQL or the SQL Server Management Studio. In this article we will describe the steps for shrinking transaction log files using T- SQL.

  1. For shrinking log files or data by using the T-SQL, you will first have to establish a connection with the Database Engine.
  2. In the appearing Standard Bar, select New Query
  3. Copy-paste the given example in query window, and press Execute.
USE TestDB;
GO
DBCC SHRINKFILE (DataFile1, 8);
GO

Essentially it reduces the size of the data file to 8 MB. You can change database name and filename as required and also specify the size of the output file.

By following this very simple procedure you will be able to shrink your data or log files, thus freeing up space, making processes faster and also making optimum use of the application without any SQL damage.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair accdb damage and sql recovery software products. For more information visit www.datanumen.com

2 responses to “6 Key Steps to Follow when Shrinking a SQL Server Log File”

  1. p.s. Since this is a “Data Recovery Blog”, what does shrinking the log file have to do with “Data Recovery”?

  2. I do appreciate people that take the time to write an article but there’s a lot wrong with the article. For example, the article states the following:

    “To avoid fragmentation, it is recommended to rebuild indexes after shrinking the log file.”

    The transaction log file has absolutely nothing to do with indexes except during Rebuilds/Reorganizes and so cannot cause fragmentation of any type of index.

    Further, no mention is made of taking the opportunity to check for initial file size and growth to control the number of VLFs that could be forming if the default initial size and growth is left to prevail. There’s no mention of regrowing the file to the anticipated size to further control the number of VLFs. There’s no mention of the steps to reestablish the log file chain if you do go to the SIMPLE recovery model.