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.