How to Shrink a SQL Server Log File
By Jatin Prajapati
In this FAQ we will see how to shrink database log files.
When we feel a application is slow in insertion, updation, or deletion is slow. This happens when log file(.ldf) grows more than the size of data file(mdf).we can
check this by using the command SP_HELPDB to check the size of database. and
DBCC SQLPERF(LOGSPACE). If database is in FULL RECOVERY MODE then take a full backup or set the
database to SIMPLE Recovery mode.
If database is in SIMPLE Recovery mode then execute the following command:
alter database <dbname> set single_user with no_wait
USE <dbname> GO DBCC SHRINKFILE (N'<dbname>' , 20)--in place if 20 specify the size of log file to be shrink in MB
alter database <dbname> set multi_user with no_wait
How to Shrink a SQL Server Log File (776 Views)