DBCC ShrinkFile

Being day 25 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC SHRINKFILE.

Description:

DBCC SHRINKFILE is used to shrink an individual log in SQL Server.

DBCC SHRINKFILE Syntax:

dbcc shrinkfile
(
    { 'file_name' | file_id }
    {
        [ , EMPTYFILE]
        | [ [, target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
    [ WITH NO_INFOMSGS ]

Example:

The following example DBCC ShrinkFile is used to shrink a log file that grew too large.

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
 FROM sys.database_files;

GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DBHealthHistory SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DBHealthHistory_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DBHealthHistory SET RECOVERY FULL;
GO
-- Be sure to do a full backup, then kick off transaction log backups

-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
 FROM sys.database_files;

Related Posts:

Blog Post Shrinking a Log File.

Notes:

For more information see TSQL Wiki DBCC shrinkfile.

DBCC Command month at SteveStedman.com is almost as much fun as chasing geoducks on the beach.

Related Videos

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!