SQL Server Database Log File has grows am over a period of time and now causing DISK space issue?

Step 1- Check the Recovery mode of the database by using

select name,recovery_model_desc from sys.databases

STEP 1 – Get logical / physical name of your database log file

use <[your dbname]>

exec sp_helpfile  —–gives the database size
or
DBCC SQLPERF(LOGSPACE) —–gives the size of Log(ldf) File size

STEP 2 – Truncate LOG File

— replace <[your database logical log filename]> with Logical file name, which we get in STEP 1

USE <[your dbname]>

GO

BACKUP LOG <[Database_log]> WITH TRUNCATE_ONLY –this works in sql server 2005 not available in 2008 and later versions

GO

STEP 3 – Shrink LOG File

— replace <[Database_log log filename]> with Logical file name, which we get in STEP 1

USE <[your dbname]>

GO

DBCC SHRINKFILE (<[Database_log log filename]>, 1)

GO

Leave a Reply

Your email address will not be published. Required fields are marked *