Shrink Multi-File Databases

USE master
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_ShrinkFiles' AND type='P')
  DROP PROC sp_ShrinkFiles
GO

--

CREATE PROC sp_ShrinkFiles @Size int=10
AS
  /*-------------------------------------------------------------------*
   | Name:     sp_Shrink_Files                                         |
   | Author:   Larry Briscoe                                           |
   | Date:     March 6, 2003                                           |
   |-------------------------------------------------------------------|
   | Purpose:  This stored procedure shrinks all database files within |
   |           the current database, including the unused portion of   |
   |           transaction log. This procedure isespecially useful for |
   |           multi-file databases.                                   |
   |-------------------------------------------------------------------|
   | Input:    @Size - Contains the desired size for each database file|
   |                   in megabytes. If not specified sp_ShrinkFiles   |
   |                   reduces the size to the 10MB for each file.     |
   |                                                                   |
   |                   This procedure will not shrink a file past the  |
   |                   size needed to store the data in the file.      |
   |-------------------------------------------------------------------|
   | Modified:                                                         |
   *-------------------------------------------------------------------*/
    SET NOCOUNT ON

    DECLARE @FileId smallint
    DECLARE @FileName sysname
    DECLARE @FileSize varchar(10)
    DECLARE @ShrunkSize varchar(10)
    DECLARE @Msg varchar(200)
    DECLARE @SQL varchar(500)

    DECLARE DataFile_csr CURSOR LOCAL FAST_FORWARD FOR
        SELECT fileid, ltrim(rtrim(name)), STR((size * .0078125000),10,2) FROM sysfiles

    SET @SQL = 'DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS'
    EXEC (@SQL)

    OPEN DataFile_csr 
    FETCH DataFile_csr INTO @FileId, @FileName, @FileSize

    SET @Msg = 'Initial Database Size: ' + (SELECT STR((SUM(size) * .0078125000),10,2) FROM sysfiles (nolock)) + 'MB...'
    PRINT @Msg

    WHILE (@@FETCH_STATUS = 0)
      BEGIN
        SET @SQL = 'DBCC SHRINKFILE (' + @FileName + ', ' + LTRIM(RTRIM(STR(@Size))) + ') WITH NO_INFOMSGS'
        --Debug Statement
        SELECT @SQL
        EXEC (@SQL)
        SET @ShrunkSize = (SELECT STR((size * .0078125000),10,2) FROM sysfiles (nolock) WHERE fileid = @FileId)
        SET @Msg = '    ShrinkFile on ' + RTRIM(LTRIM(@FileName)) + ' from ' + LTRIM(@FileSize) + 'MB to ' + LTRIM(@ShrunkSize) + 'MB...'
        PRINT @Msg

        FETCH DataFile_csr INTO @FileId, @FileName, @FileSize
      END

    CLOSE DataFile_csr 
    DEALLOCATE DataFile_csr 

    SET @Msg = 'Shrunk Database Size: ' + (SELECT STR((SUM(size) * .0078125000),10,2) FROM sysfiles (nolock)) + 'MB...'
    PRINT @Msg

    SET NOCOUNT OFF
GO

sp_MS_marksystemobject sp_ShrinkFiles
GO