Tuesday, 5 February 2013

SQL Server 2008 Scripts: Shrink a database

My current Sharepoint project has several search crawls and data migrations firing during deployments, which is creating some space concerns from my poor development VM. I have added the following script as a batch file on my desktop to free up space in my local SQL server by doing a dump and shrink.

Yes, I know that dumping and shrinking transaction logs is not best practice, but this a dev environment and that which does not kill it allows me to get my work done.

The script uses the great (undocument, but readily document on the internet) master..sp_msforeachdb. A simple one line piece of SQL does the trick:

osql.exe -S. -E -q "exec master..sp_msforeachdb 'ALTER DATABASE [?] SET RECOVERY SIMPLE; DBCC SHRINKDATABASE ([?], 1); ALTER DATABASE [?] SET RECOVERY FULL;'"

No comments:

Post a comment