Shrinking several hair types in SQL Server databases

Method One:

Use database name

Select Name,size from Sys.database_files

ALTER database name SET RECOVERY simple with no_wait

ALTER database name SET RECOVERY simple

Use database name

DBCC shrinkfile (N ' log filename ', 0,truncateonly)

ALTER database name SET RECOVERY full with no_wait

ALTER database name SET RECOVERY full

Method Two:

Backup LOG [your database name] with NO_LOG
Backup LOG [your database name] with TRUNCATE_ONLY
DBCC shrinkdatabase ([your database name])

Description

Backup LOG specifies that only the transaction log is backed up. The log is from the last log backup that was successfully executed to the end of the current log. After you back up the log, you may truncate the space that is no longer needed for transactional replication or active transactions.

No_log | Truncate_only

By discarding all logs except the activity log, you can delete the inactive log section and truncate the log without backing up the replication log. This option frees up space. Because log backups are not saved, it is not necessary to specify a backup device. No_log and truncate_only are synonymous.

Here's a question, what is the activity log, Microsoft's explanation is:

The portion of the log file from the first log record that must exist to ensure that the database is successfully rolled back to the last written log record is called the active part of the log, which is the activity log. This is the section of the log required for full database recovery. You can never truncate any part of the activity log.

DBCC shrinkdatabase shrinks all data and log files for a particular database, and this is the simplest way to use it.

But there is one problem:

In some cases where data security requirements are low, or if the backup frequency is high, the use of a direct truncation log is not as easy as using the simple recovery model. Microsoft said: Under the simple recovery model, the transaction log will be automatically truncated after each data backup, that is, the inactive logs will be deleted. Because log truncation often occurs, there is no transaction log backup. This simplifies backup and restore. However, without a transaction log backup, it is not possible to recover to a point in time of failure.

That is: If you do not use the log to recover data (I do not use it myself, I use redgate timing high-frequency backup), then the simple mode is the best solution.

Shrinking several hair types in SQL Server databases