Thursday, June 21, 2012

Reduce the size of Log(.ldf) File in SQL Server

Today i had a problem. When i checked my database LDF file (log) file it’s some where around 200GB and growing. I was trying to shrink the file, but it's upto few MB's. So that was not helpful. Found a solution, First make the database to SIMPLE Recovery mode and do it.

-– Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [databasename]
SET RECOVERY SIMPLE;
GO
–- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ([log file name], 1);
GO
– Reset the database
ALTER DATABASE [databasename]
SET RECOVERY FULL;
GO
Also you can do the above steps in UI,
  1. Right click the database you want to delete the log, go to Properties, select Options, then select Simple in Recovery Model and click Ok. 
  2. Then again go to properties -> Files -> change _log file size by changing the size in "Initial Size (MB)" column to 1 -> then click ok 
  3. Then Again change the recovery model to full. This will free some log space. If you want the earlier log files, you should've kept the Full Backup of database before deleting log file.

1 comment: