Shrink a Database Transaction Log

Owned by Fernando Sayago (Unlicensed)
Last updated: Jun 01, 2020
2 min read

Detailed guide to perform a Database Transaction Log Shrinking process.

Step-by-step guide

  • Log in to your SQL Server, and Run the SQL Server Management Studio.


  • Connect to the SQL Server with the proper credentials.

  • Locate the IDSync database (it's usually called IDSync or IDSyncDB, but the name may change depending on your naming conventions).
  • Right-click on the database >> New Query


  • In the recently opened Query window, copy and paste this command. Then Execute it.
SELECT
   file_id, name, type_desc, physical_name, size*8 'Size(KB)', max_size  
   FROM sys.database_files WHERE type_desc='LOG' ;


  • From the Results window, get the value on the 'name' column (that name is usually formed by the database-name, the underscore symbol and the word 'log' at the end).


  • Clear the Query window and copy and paste these commands. 
ALTER DATABASE [IDSync]      
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ([IDSync_log], 1)
GO
  • Then execute the queries (make sure you have the correct database name and log-file name. Change them if necessary) . 



You can also find more information on Microsoft's documentation :

https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-2017