How to Reduce the Size of Your Local SQL Databases on a Schedule

> 5 minutes could save you 14% or more on disk space and car insurance

August 25, 2025

Overview

Sometimes tough choices have to be made regarding disk space when running multiple Sitecore implementations on one enterprise development machine. In this post, we will demonstrate how to reduce the size of your Sitecore databases on-demand and on-schedule.

This is only intended for local development databases.

Before You Start: How Big Are Your DBs?

For fun, run this command to see how big all your databases are before you run the reduction scripts:

SELECT
    CAST(SUM(size) * 8.0 / 1024 / 1024 AS DECIMAL(18,2)) AS TotalSizeGB
FROM sys.master_files;

I reduced my total database sizes by 14%.

Step 1: Reclaim Disk Space On-Demand

When databases are restored via BACPAC or BAK files, there is some post-restore maintenance that can be done to reclaim disk space.

SQL Statements This sets the recovery model to simple, which will reduce the size of your transaction log.

ALTER DATABASE {your_database_name_here} SET RECOVERY SIMPLE;

To run this on all of your user databases, run the following script:

DECLARE @databaseName nvarchar(max);
DECLARE @sql nvarchar(max);
DECLARE database_cursor CURSOR FOR
SELECT
	d.[name],
	'ALTER DATABASE [' + d.[name] + '] SET RECOVERY SIMPLE;' AS [Query]
FROM
	[sys].[databases] AS d
WHERE
	d.[database_id] > 4
	AND
	d.[recovery_model_desc] != 'SIMPLE'
ORDER BY
	d.[name]
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @databaseName, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Altering database ' + @databaseName;
	EXEC(@sql)
	FETCH NEXT FROM database_cursor
	INTO @databaseName, @sql
END
CLOSE database_cursor;
DEALLOCATE database_cursor;

This shrinks the transaction log file, which you will want to do after changing the recovery model.

--Get the name of the log file(s)
SELECT [file_id], [name] FROM [sys].[database_files] WHERE [type_desc] = 'LOG';
--Shrink the log file.
DBCC SHRINKFILE (N'{copy_log_file_name_here}' , 0, TRUNCATEONLY);

To run this on all of your user databases, run the following script:

DECLARE @databaseName nvarchar(max);
DECLARE @databaseFileName nvarchar(max);
DECLARE @sql nvarchar(max);
DECLARE database_cursor CURSOR FOR
SELECT
	d.[name],
	mf.[name],
	'USE [' + d.[name] + ']; DBCC SHRINKFILE(N''' + mf.[name] + ''', 0, TRUNCATEONLY);' AS [Query]
FROM
	[sys].[master_files] AS mf 
	INNER JOIN [sys].[databases] AS d ON
		mf.[database_id] = d.[database_id]
WHERE
	d.[database_id] > 4
ORDER BY
	d.[name]
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @databaseName, @databaseFileName, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'Shrinking database file ' + @databaseFileName + ' of database ' + @databaseName;
	EXEC(@sql)
	FETCH NEXT FROM database_cursor
	INTO @databaseName, @databaseFileName, @sql
END
CLOSE database_cursor;
DEALLOCATE database_cursor;

Step 2: Set Up a Schedule

We are going to use SQL Server Agent jobs to run the queries above.

2.1: Configure the SQL Server Agent Service

Change the Startup Type to Automatic (Delayed Start) before you start it, as the default Startup Type is Disabled.

SQL Server Agent Service Configuration Screenshot

2.2: Create the SQL Server Agent Job

Execute the following SQL script to create the SQL Server Agent job:

USE [msdb]
GO
/****** Object:  Job [Reduce Database Sizes]    Script Date: 8/6/2025 12:25:07 PM ******/
BEGIN TRANSACTION
DECLARE @ownerLoginName NVARCHAR(128);
-- Get the current user's login name
SET @ownerLoginName = SUSER_SNAME();
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 8/6/2025 12:25:07 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Reduce Database Sizes',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'No description available.',
		@category_name=N'Database Maintenance',
		@owner_login_name=@ownerLoginName, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Update to Simple Recovery]    Script Date: 8/6/2025 12:25:07 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update to Simple Recovery',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=3,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'DECLARE @databaseName nvarchar(max);
DECLARE @sql nvarchar(max);
DECLARE database_cursor CURSOR FOR
SELECT
	d.[name],
	''ALTER DATABASE ['' + d.[name] + ''] SET RECOVERY SIMPLE;'' AS [Query]
FROM
	[sys].[databases] AS d
WHERE
	d.[database_id] > 4
	AND
	d.[recovery_model_desc] != ''SIMPLE''
ORDER BY
	d.[name]
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @databaseName, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT ''Altering database '' + @databaseName;
	EXEC(@sql)
	FETCH NEXT FROM database_cursor
	INTO @databaseName, @sql
END
CLOSE database_cursor;
DEALLOCATE database_cursor;',
		@database_name=N'master',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Shrink Database Files]    Script Date: 8/6/2025 12:25:07 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Shrink Database Files',
		@step_id=2,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'DECLARE @databaseName nvarchar(max);
DECLARE @databaseFileName nvarchar(max);
DECLARE @sql nvarchar(max);
DECLARE database_cursor CURSOR FOR
SELECT
	d.[name],
	mf.[name],
	''USE ['' + d.[name] + '']; DBCC SHRINKFILE(N'''''' + mf.[name] + '''''', 0, TRUNCATEONLY);'' AS [Query]
FROM
	[sys].[master_files] AS mf
	INNER JOIN [sys].[databases] AS d ON
		mf.[database_id] = d.[database_id]
WHERE
	d.[database_id] > 4
ORDER BY
	d.[name]
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @databaseName, @databaseFileName, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT ''Shrinking database file '' + @databaseFileName + '' of database '' + @databaseName;
	EXEC(@sql)
	FETCH NEXT FROM database_cursor
	INTO @databaseName, @databaseFileName, @sql
END
CLOSE database_cursor;
DEALLOCATE database_cursor;',
		@database_name=N'master',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily at 1pm',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20250806,
		@active_end_date=99991231,
		@active_start_time=130000,
		@active_end_time=235959,
		@schedule_uid=N'2a890b02-284f-46fd-bab9-dbb267035bd2'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

You should now see this job in SQL Server Management Studio under SQL Server Agent. It is configured to run daily at 1pm, but you can adjust this if needed.

SQL Server Agent Job in Management Studio

You can right click that job and click View History to view the history of the job to make sure it is running without issue. You can also right click it and start it manually (with Start Job at Step…).

Log File Viewer History