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 be a cause performance issues, also it will take a lot of space up 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 example we have SharePoint server also we have many of content Database and PWA site database as well which will get increase always which we can control using script.

We need to follow 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 below command on the new query page then run 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.

Thanks ☺