Shrinking Log file of Database in Production Environment

Let's take a look at how we shrink our log files in sql server environment.


Any database on sql server has two type of files.
1. .mdf files
2. ldf files

MDF files are transitional data files. There is one primary mdf files and it can have many secondary files. Similarly ldf are log transitional files with one primary and many secondary).

Step 1. Lets check  all the log space for all the database on a server. To do this run

DBCC SQLPERF (logspace);

What this return is DatabaseName, LogSize ( this is combined logsize if you have multiple log file for a particular database), LogSpace used, and status.

Step 2. Find the database whose log file you want to shrink. let's say I want to shrink tempdb datbase log file (not the best idea but for explanation purpose, its good enough).


So we run

SP_HELPDB DatabaseName;

SP_HELPDB tempdb

So this will return you all the mdf, ldf and ndf file information, where it is located, whats the size (in KB),  etc. You want to make sure to check the size of log files in GB or MB. To do so run Select "size of the file/1024" to give in MB and "size of the file/1024/1024" in GB


 --Convert KB to GB to check size of log files
 SELECT 111111111/1024/1024

Step 3.

To your database from Object Explorer, select the database where you want to shrink log file, Right click Task, then shrink and then files.

Select file type as log and select "Released unused space".

You can click Ok or generate a script.

 USE [tempdb]
GO
DBCC SHRINKFILE (N'Templog' , 0, TRUNCATEONLY)
GO


This is how you shrink log files