====== Microsoft - Microsoft SQL Server - SQL - Shrink vs Truncate ======

===== Shrink =====

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

<WRAP info>
**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.

</WRAP>


<WRAP important>
**WARNING:**  There is an inherent risk of data loss should this process fails.

</WRAP>


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

<code sql>
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
</code>

----

===== 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.

<WRAP info>
**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.

</WRAP>

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

<code sql>
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
</code>


<WRAP info>
**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.

</WRAP>

----