Shrink multiple database files using sp_MSForEachDB

We have recently cleared out old data from our production database. The database is 3 TB with 1.4 TB empty this however poses a problem on the development and QA instance as we are not utilizing space by having 6 - 8 databases with 1.4 TB empty space especially with the space constraints we have on development. I want to setup a job to shrink the development databases using the below code

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (''?'' , 10)' 

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (''?'' , 0, TRUNCATEONLY)'

I am getting the following error, "Could not locate file 'Test' for database 'Test' in sys.database_files. The file either does not exist, or was dropped."
The database has multiple data files. How could i improve my code to accommodate
the multiple data files.

The question mark will evaluate to the database name, not the file name you're trying to shrink.

For instance:

EXEC master.sys.sp_MSforeachdb ' USE [?]; PRINT N''?''; ';

Will return (on my server)

master
tempdb
model
msdb
SUPERUSER
StackOverflow
StackOverflow_CS
Crap
DBAtools
StackOverflow2010
SUPERUSER_CX
ಠ_ಠ
StackOverflow2010ಠ_ಠ

DBCC SHRINKFILE doesn't take that as an argument:


DBCC SHRINKFILE (

{ file_name | file_id }

{ [ , EMPTYFILE ]

| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]

} ) [ WITH NO_INFOMSGS ]

If you only have a .mdf and a .ldf, you can (likely, though not definitely) replace your code with:

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (1 , 10)' 

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (2 , 0, TRUNCATEONLY)'

More verbose code to look up file ids, etc. is left as an exercise to the reader.

If you want to just shrink the whole thing, use DBCC SHRINKDATABASE instead. That takes a database name, and will work with your original code.


DBCC SHRINKDATABASE ( database_name | database_id | 0

[ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

Of course, this can cause all sorts of problems, and I wouldn't wanna do it.

StackExchange Database Administrators Q#206234, answer score: 4