microsoft:microsoft_sql_server:sql:shrink_vs_truncate
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
| microsoft:microsoft_sql_server:sql:shrink_vs_truncate [2022/10/18 16:57] – peter | microsoft:microsoft_sql_server:sql:shrink_vs_truncate [2022/10/18 17:14] (current) – peter | ||
|---|---|---|---|
| Line 5: | Line 5: | ||
| The **shrink** command is used to reduce the physical log file size of a database. | The **shrink** command is used to reduce the physical log file size of a database. | ||
| - | | + | <WRAP info> |
| - | * There is an inherent risk of data loss should this process fails. | + | **NOTE: |
| - | 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. | + | 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. | * Hence, manual intervention may not be required for this design choice. | ||
| Line 19: | Line 19: | ||
| * Hence, specific point-in-time data cannot be instantaneously restored without retrieving certain log files in whole. | * 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. | * Hence, shrink operations are often necessary for these types of recovery models. | ||
| + | |||
| + | </ | ||
| + | |||
| + | |||
| + | <WRAP important> | ||
| + | **WARNING: | ||
| + | |||
| + | </ | ||
| + | |||
| Here is a sample T-SQL to perform this task on a Full-Recovery-Model database: | Here is a sample T-SQL to perform this task on a Full-Recovery-Model database: | ||
| Line 37: | Line 46: | ||
| 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 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. | * This may be interpreted as actual deletion. | ||
| * It is notable that sql backup jobs will also automatically truncate log files. | * 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. | * 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: | Here is a sample T-SQL to perform this task manually: | ||
microsoft/microsoft_sql_server/sql/shrink_vs_truncate.1666112250.txt.gz · Last modified: 2022/10/18 16:57 by peter