SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Automate scripting of database file shrink

By Jason S Wong,

I have a large number of databases that I can free up significant space, but to avoid resource contention, I can only do this at nights or weekends. Here is the script to automate this task with scheduler..

Change line 99 from Print to exec, you will be able to execute this as an automated job. It works for me. Please test it where it suits your purpose.


Use percent free factor on line 79 to filter the database files you want to shrink.

Some improvements can be further made, for those who want to jump in, such as write it into a SP.

Generate commands using print, then put them into SQLAgentScheduledJob or change print to exec and put this code into SQLAgentScheduledJob.


The possible reasons for doing this,

1) after a large cleanup, truncate table or delete large number of records

2) inherited DBs are carrying unused space, short of space and cannot obtained more disk space due to hardware infrastructure or expense.

3) cleanup DBs to move to archive or save space, for those who lease space, it gets expensive to carry unused space.

4) for production databases, change line 69, 70 to

CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) *1.20 AS DECIMAL(15,0))
) AS VARCHAR(20) )+ ')'

to reserve 20% free for future growth, then run my index defrag code (I run it weekly Sunday)

Use the control parameters to run this job in the night, it automatically complete DBA maintenance tasks (which mostly can only be run at night anyhow). All you have to is to check in the morning.

I am suggesting use this script to automate shrink script commands where when it is needed. For those who has not recently checked your DB unused space, you may be suprised how much free space you carried around unused.

Total article views: 2373 | Views in the last 30 days: 3
Related Articles

Change Database Collation

A stored procedure to automate database collation change


Automated Monitoring Database Size Using sp_spaceused

Keeping track of the amount of space in a database is something every DBA needs to do or face the dr...


Database Restore Automation

Read about Paul Brewer's Framework (Version 2) for Database Restore Automation.


Automate Test Database Restoration

Automate test database restorations from your production system.


DB Change Management: An Automated Approach - Part 3

Part 3 of Darren Fullers series on automating change management for SQL Server. A must read if you p...