...
https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/
In short you need to set your database “Recovery Model” to Simple and auto shrink your database
...
If you want to keep the “Recovery Model” to Full you need to make a full backup, then a log backup then shrink the This script will shrink the database and then the log change the name of “Backup” database here
| Code Block | ||
|---|---|---|
| ||
BACKUPALTER DATABASE [WIP_MT_Release_hotfix] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\WIP_MT_Release_hotfix.bak' WITH NOFORMAT, NOINIT, NAME = N'WIP_MT_Release_hotfix', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO BACKUP LOG [WIP_MT_Release_hotfix] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\WIP_MT_Release_hotfix.log_bak' WITH NOFORMAT, NOINIT, NAME = N'WIP_MT_Release_hotfix_log', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GOBackup SET RECOVERY SIMPLE GO DBCC SHRINKFILE (Backup, 1) GO DBCC SHRINKFILE (Backup_log, 1) GO ALTER DATABASE Backup_TDD SET RECOVERY FULL |
Shrink the log after backup
...