SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Introduction

You know there is always an issue - the log file growing very fast and big. If you have plenty of storage, then this might not be a problem for you. Anyway, this is no exception in the latest version of SQL, we still have to do something to truncate and shrink these files.

Implementation

1) Let’s first check the log file size.

SELECT

--DB_NAME(database_id) AS DatabaseName,

--Physical_Name,

Name AS Logical_Name,

(size*8)/1024 SizeMB

FROM

sys.master_files

WHERE

DB_NAME(database_id) = 'tempdb'

GO

Output

2) Now truncate the log file.

USE tempdb;

GO

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

ALTER DATABASE tempdb

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE(tempdb_log, 1); --file_name is the logical name of the file to be shrink

GO

-- Reset the database recovery model.

ALTER DATABASE tempdb

SET RECOVERY FULL WITH NO_WAIT;

GO

3) Let’s check the log file size.

SELECT

--DB_NAME(database_id) AS DatabaseName,

--Physical_Name,

Name AS Logical_Name,

(size*8)/1024 SizeMB

FROM

sys.master_files

WHERE

DB_NAME(database_id) = 'tempdb'

GO

Output

Consider the following information when you plan to shrink a file:

• Make a full backup of your database before shrink the database file.
• From setting the database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. In this case, you should consider increasing the Growth Rate of your Database to keep the performance under control.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

Reference: http://technet.microsoft.com/en-us/library/ms189493.aspx