How to Shrink the Data Base Log files in SQL server.

When database getting larger you will suffer from the disk capacity problems. For solve this problem Shrink the database log file is the best way.

Today I`m going to discuss about how to Shrink the database log file using SQL query.

Before you shrink the Log file there are some Limitations and restrictions

• ·        You cannot shrink the database while being backed up. And also cannot backup the database while the shrink is ongoing.

• ·        For shrink the Database log files user required sysadmin fixed server role or the db_owner fixed database role.

USE [AddyourDBNameHere]; 

GO 

-- Truncate the log by changing the database recovery model to SIMPLE. 

ALTER DATABASE [AddyourDBNameHere] 

SET RECOVERY SIMPLE; 

GO 

-- Shrink the truncated log file to 1 MB.  Add the logical database log file name

DBCC SHRINKFILE (AddyourDBNameHere _log, 1); 

GO 

-- Reset the database recovery model. 

ALTER DATABASE [AddyourDBNameHere] 

SET RECOVERY FULL; 

GO

Execute this query

Done.

Thank you.