Shrink Database

Code snippets for shrinking database and database log files for EC2 and OnPrem.

This will not work on AWS RDS databases.

Log File

Shrink Database Log File
USE DBNAME; 
ALTER DATABASE DBNAME SET RECOVERY SIMPLE;
DBCC SHRINKFILE (DBNAME_log, 1);
ALTER DATABASE DBNAME SET RECOVERY FULL;

Data File

Click on database and navigate to REPORTS / STANDARD REPORTS / DISK USAGE and verify that there is UNALLOCATED SPACE that can be removed.

Set Recovery Mode
use master;
ALTER DATABASE [SSISDB] SET RECOVERY SIMPLE WITH NO_WAIT

When this is the SSISDB, I consider reducing the default RETENTION WINDOW of 365 days to something less, like 30 or 7. Then I make sure CLEANUP is enabled.

SSISDB Check
Use ssisdb;
EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=7;

select * from [catalog].catalog_properties

I DISABLE all SQL AGENT JOBS that use this database.

For the SSISDB only, I run the CLEANUP command.

SSISDB Cleanup
use ssisdb;
EXEC [internal].[cleanup_server_retention_window];

Click on the database and navigate to: TASKS / SHRINK / DATABASE

Alternatively I sometimes run this code:

Shrink Database
use ssisdb;
DBCC SHRINKDATABASE(N'SSISDB', 5 );

Then I remember to reset the RECOVERY to FULL.

Set Recovery Mode
use master;
ALTER DATABASE [SSISDB] SET RECOVERY FULL WITH NO_WAIT;

Last, I ENABLE the SQL AGENT JOBS that I disabled earlier.