I have a need to execute DBCC SHRINKFILE for multiple db's within the same sproc. I could create multiple sprocs so it runs within the given context, but I was curious if there were alternatives?
A:
EXEC sp_MSForEachDB 'USE ? DBCC SHRINKFILE (fileid, targetsize)'
The undocumented sp_MSForEachDB is your friend here
Edit, after Raj's comment
EXEC sp_MSForEachDB '
USE ?
IF DB_NAME() IN (''DB1'', ''DB1'', ''DB1'')
DBCC SHRINKFILE (fileid, targetsize)
'
gbn
2010-03-30 20:50:44
This will run for all databases on the server that the current account has permissions for.
Raj More
2010-03-30 20:51:40
@Raj More: good point, updated
gbn
2010-03-30 20:53:49
Providing a worst practice solution, that may answer the question being asked but will cause problems later on, isn't really helpful to the community. SQL Server isn't MS Access, shrinking is bad unless there is no way the space be required again, for example a 1TB database that gets a new monthly purging process implemented to groom database size, a one time shrink after the initial purge makes sense, but then it shouldn't be shrunk again if maintained.
Jonathan Kehayias
2010-03-30 23:27:10
Thanks for the input, you've made me re-think my approach:"...for example a 1TB database that gets a new monthly purging process implemented to groom database size, a one time shrink after the initial purge makes sense, but then it shouldn't be shrunk again if maintained"
2010-03-31 15:10:41
+2
A:
Your best approach is, by far, to never write a script that shrinks a database file. From the million destructive things you can do with a database, shrinking it is right there in top 3. See Auto-shrink – turn it OFF!.
Remus Rusanu
2010-03-30 23:14:08
Totally agree with Remus on this one. The Log file is the most expensive of all files to grow since it has to be zeroed out during initialization and can't use instant file initialization. SQL MVP Aaron Bertrand has a great blog post with lots of other references beyond Paul Randal's provided by Remus:http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx
Jonathan Kehayias
2010-03-30 23:22:02