Code snippets for shrinking database and database log files for EC2 and OnPrem.
This will not work on AWS RDS databases.
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.
use master;
ALTER DATABASE [SSISDB] SET RECOVERY SIMPLE WITH NO_WAITWhen 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.
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.
use ssisdb;
EXEC [internal].[cleanup_server_retention_window];Click on the database and navigate to: TASKS / SHRINK / DATABASE
Alternatively I sometimes run this code:
use ssisdb;
DBCC SHRINKDATABASE(N'SSISDB', 5 );Then I remember to reset the RECOVERY to FULL.
use master;
ALTER DATABASE [SSISDB] SET RECOVERY FULL WITH NO_WAIT;Last, I ENABLE the SQL AGENT JOBS that I disabled earlier.