How To Shrink The Size Of A Transaction Log (.LDF) In SQL Using SQL Server Management Studio 2016

Today we are writing about how to shrink the size of a transition log (.LDF) for SQL database. It can cause performance issues, also it will take a lot of valuable disk space and consume resources when you back up. Therefore it's imperative to periodically maintain the database to keep the .ldf file under control.

Just for an example we have SharePoint server; also we have many content Databases and PWA site databases as well which will get increased always which we can control using script.

We need to follow the below steps to complete the task.

• Connect the SQL server instance from SSMS
• We need expand the Databases node and expand User Databases
  
  
  
  
• Right-click the database, and click Properties, which opens the Database Properties dialog box.
• In the Select a page pane, click files.
  
  
  
  
• Now copy the file name which is ending with .LDF, and do the same for all Database log file and save in the note pad.
• Now click on Management option from SQL instance in left side and expand the same.
• Expand all Database name and right click on the same then create new Query.
  
  
  
  
• Then run the below command on the new query page then run the below command with log file which you have copied earlier.
  

  USE "Sp2016_SharePoint_ConfigDB"
  GO
  ALTER DATABASE "SP2016_SharePoint_ConfigDB"
  SET RECOVERY SIMPLE
  GO
  ALTER DATABASE "SP2016_SharePoint_ConfigDB"
  SET RECOVERY FULL
  GO
  DBCC SHRINKFILE("SP2016_SharePoint_ConfigDB_log", 1)

• Change the highlighted and click on execute or press F5 and the see the result
  
  
  
  
• Check your log file before running and check the same after running successfully.
  
  
  
  
• After running the above SQL shrink file command output will be like below:
  
  
  
  
• Now see the content DB space.