Table of Contents

Microsoft - Microsoft SQL Server - SQL - Shrink vs Truncate

Shrink

The shrink command is used to reduce the physical log file size of a database.

NOTE: That can be understood as de-fragmentation, file compaction, and resetting file physical pointers.

Often databases that are setup with the simple recovery model have corresponding backup jobs that would run during non-business hours (e.g. 2AM) as this will automatically shrink log files.

  • Hence, manual intervention may not be required for this design choice.

Other recovery models such as FULL and BULK-Logged will require special considerations.

  • The former will store all transactions Data Definition Language (DDL) and Data Manipulation Language (DML) in transaction log files with a sequential order.
    • These logs will not auto-truncate during CHECKPOINT operations.
  • The latter, BULK-Logged is similar to FULL with the main difference in that certain bulk operations are minimally logged.
    • Hence, specific point-in-time data cannot be instantaneously restored without retrieving certain log files in whole.
    • Hence, shrink operations are often necessary for these types of recovery models.

WARNING: There is an inherent risk of data loss should this process fails.

Here is a sample T-SQL to perform this task on a Full-Recovery-Model database: 

USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (TestDb, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

Truncate

The truncate command changes the status of one or more virtual log files (VLF) from active to inactive, which marks those files for overwriting or reuse.

NOTE: The truncate command can also be substituted with NO_LOG, which will purge a database’s transaction log.

  • This may be interpreted as actual deletion.
    • It is notable that sql backup jobs will also automatically truncate log files.
    • However, such process will not delete files unless NO_LOG is specified after the backup job.

Here is a sample T-SQL to perform this task manually: 

USE [master]
GO
DBCC SHRINKFILE(TestDb_Log, 1)
BACKUP LOG TestDb WITH TRUNCATE_ONLY -- NO_LOG (to delete log file)
DBCC SHRINKFILE(TestDb_Log, 1)
GO

NOTE:

  • Adequate disk space provisioning for log files are necessary for the health and longevity of SQL Servers.
    • It is recommended that log files be placed on a separate volume from the database files for better performance and administration.
  • Assuming Simple Recovery models, daily database backup duration should be estimated to account for disk space truncation as that would occur automatically.
  • Assuming Full or Bulk_Logged Recovery models, the ALTER DATABASE … SET RECOVERY SIMPLE command as precursor to the SHRINK command is necessary to reduce disk usage on a periodic basis.
    • This sequence should only be executed after each BACKUP operations.