Cleaning up Disk Space on Your Machine

One recurring annoyance with working on lots of different projects is that over time your disk just gets full. Working in many MS SQL Server databases over the years I've noticed that there is a pretty simple way I can usually reclaim a lot of diskspace from SQL Server on my machine. I currently have over 100 development databases on my machine and a lot of those were one time uses. I'm sure I could remove a bunch of them, but in a pinch, this allowed me to very quickly reclaim over 30GB of disk space

Dynamically generate the dbcc shrink database syntax for all of the databases on my box

select 'dbcc shrinkdatabase([' + name + '], TRUNCATEONLY);' from sys.databases where name <> 'master'

copy the output into a sql server query window and run it

I had issues with one database because it was restoring. After removing that database from the list of queries, it ran rather quickly and I got a gobs of disk space back. If you have databases on your machine that you do not want to shrink, you can remove those from the list

For more information on SHRINKDATABASE

http://msdn.microsoft.com/en-us/library/ms190488.aspx

Update

You can use the following to generate a dynamic sql statement and execute it using EXEC

DECLARE @SQL VARCHAR(MAX)

select @SQL = COALESCE(@SQL,'') + '
dbcc shrinkdatabase([' + name + '], TRUNCATEONLY);' from sys.databases
where name not in ( 'master', 'model', 'tempdb', 'msdb')

print cast(@SQL as ntext)

EXEC(@SQL)