Shrink

The shrink command is to reduce the physical log file size of a database. That can be understood as de-fragmentation, file compaction, and resetting file physical pointers. There’s an inherent risk of data loss should this process fails.

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.

Here’s 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. 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’s 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’s 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
DBA Comment:
  1. Adequate disk space provisioning for log files are necessary for the health and longevity of SQL Servers. It’s recommended that log files be placed on a separate volume from the database files for better performance and administration.
  2. Assuming Simple Recovery models, daily database backup duration should be estimated to account for disk space truncation as that would occur automatically.
  3. 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.
Categories: