Shrink Multiple Database Files Using sp_MSForEachDB in SQL Server

dbccsql server

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.

Best Answer

@sp_BlitzErik has correctly identified the problem, but I'd propose a different solution: use a one-time script that creates your SHRINKFILE statements, check them for sanity, then run them manually or put them into your agent job:

SELECT  dbname = DB_NAME(),
        file_name = name, 
        TotalMB = CONVERT(decimal(12,1),size/128.0),
        UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
        FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
        Command = CONCAT('USE ', DB_NAME(), '; DBCC SHRINKFILE (name = ',
              [name], ', size = ', 
              convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)), 'MB)')
 FROM sys.database_files WITH (NOLOCK)
 WHERE type_desc = 'ROWS'
 ORDER BY file_id;

Run this once from each database, it should return the total and used size for each data file (it skips log files, you can shrink those instantly by hand afterwards), and an example SHRINKFILE statement that gives you a target of 15% free space in the file, calculated from the current used space:

USE myDB; DBCC SHRINKFILE (name = myDBData, size = 148910MB)

You will need to check the results for sanity, if the file already has less than 15% free space, then the SHRINKFILE statement will specify a larger size than it currently has, so skip it (its already small enough).

After you've shrunk all the data files, pick a target size for each log file (I typically use 10-25% of the data file size), and shrink those by hand. This may depend on the recovery model, and also by how much activity these dbs get in that environment.

Related Question