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;'"
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;'"
Comments
Post a Comment