Now we are interested in shrinking the log file. We need to run DBCC command against the log file. But to run against, we first need to know the log file name.

SELECT name FROM Sysfiles

The above query will return the database and log file name. Now we got the log file name.

Let’s run the DBCC command now. The following command shrinks the log file.

DBCC SHRINKFILE('SampleDB_Log', 2)

Here 'SampleDB_Log' is the log file and 2 is target file size in MB.

Even after executing the command, if you have not got the expected size of the log file, we might have to truncate the logs. The following command would truncate the transaction log

BACKUP LOG SampleDB WITH TRUNCATE_ONLY

Here SampleDB is the database name.

Now again run the shrink file command

DBCC SHRINKFILE('SampleDB_Log', 2)

Now you will be seeing the target size achieved. That’s it.

Happy Programming!!!