Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 2286 | Views in the last 30 days: 5
 
Related Articles
ARTICLE

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...

ARTICLE

Database Restore Automation

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

SCRIPT

Automate Test Database Restoration

Automate test database restorations from your production system.

ARTICLE

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...

FORUM

Sql 2000, Database don't have available space

Sql 2000, Database don't have avialable space

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones