Return to Answer

added 322 characters in body

Source Link

edited May 31, 2017 at 9:27

McNets

23.9k
11
50
89

DECLARE @DBFileName SYSNAME

DECLARE @TargetFreeMB INT

DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500 -- Set Increment to shrink file by in MB SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @sql VARCHAR(8000) DECLARE @SizeMB INT DECLARE @UsedMB INT

SELECT @SizeMB = size / 128. FROM sysfiles WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB ,[StartUsedSpace] = @UsedMB ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

BEGIN SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

DECLARE @DBFileName SYSNAME

DECLARE @TargetFreeMB INT

DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500
-- Set Increment to shrink file by in MB
SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2),
round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @sql VARCHAR(8000)
DECLARE @SizeMB INT
DECLARE @UsedMB INT

SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB
    ,[StartUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

BEGIN
    SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

    PRINT 'Start ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    EXEC (@sql)

    PRINT 'Done ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    SELECT @SizeMB = size / 128.
    FROM sysfiles
    WHERE NAME = @DBFileName

    SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

    SELECT [FileSize] = @SizeMB
        ,[UsedSpace] = @UsedMB
        ,[DBFileName] = @DBFileName

END

SELECT [EndFileSize] = @SizeMB ,[EndUsedSpace] = @UsedMB ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

END

SELECT [EndFileSize] = @SizeMB
    ,[EndUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

    ,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @DBFileName SYSNAME

DECLARE @TargetFreeMB INT

DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500 -- Set Increment to shrink file by in MB SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @sql VARCHAR(8000) DECLARE @SizeMB INT DECLARE @UsedMB INT

SELECT @SizeMB = size / 128. FROM sysfiles WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB ,[StartUsedSpace] = @UsedMB ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

BEGIN SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

PRINT 'Start ' + @sql
PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

EXEC (@sql)

PRINT 'Done ' + @sql
PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [FileSize] = @SizeMB
    ,[UsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

END

SELECT [EndFileSize] = @SizeMB ,[EndUsedSpace] = @UsedMB ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @DBFileName SYSNAME

DECLARE @TargetFreeMB INT

DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500
-- Set Increment to shrink file by in MB
SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2),
round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @sql VARCHAR(8000)
DECLARE @SizeMB INT
DECLARE @UsedMB INT

SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB
    ,[StartUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

BEGIN
    SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

    PRINT 'Start ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    EXEC (@sql)

    PRINT 'Done ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    SELECT @SizeMB = size / 128.
    FROM sysfiles
    WHERE NAME = @DBFileName

    SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

    SELECT [FileSize] = @SizeMB
        ,[UsedSpace] = @UsedMB
        ,[DBFileName] = @DBFileName
END

SELECT [EndFileSize] = @SizeMB
    ,[EndUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

    ,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a

Source Link

created May 18, 2017 at 19:37

user1207758

You have heard all of the warnings about shrinking databases and they are all true. It will fragment your indexes and is in general, muck up your database and shouldn't be done on a production system.

But, I generally do so on a weekly basis when I restore a backup on my workstation due to space on my SSD drive. Mind you, I didn't write this script but found it years ago. On other databases [250 GB], I created an SSIS package that will transfer the tables that I need and then recreate the indexes for that oh so fresh index feel.

DECLARE @DBFileName SYSNAME

DECLARE @TargetFreeMB INT

DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500 -- Set Increment to shrink file by in MB SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a

DECLARE @sql VARCHAR(8000) DECLARE @SizeMB INT DECLARE @UsedMB INT

SELECT @SizeMB = size / 128. FROM sysfiles WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB ,[StartUsedSpace] = @UsedMB ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

BEGIN SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

PRINT 'Start ' + @sql
PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

EXEC (@sql)

PRINT 'Done ' + @sql
PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

SELECT @SizeMB = size / 128.
FROM sysfiles
WHERE NAME = @DBFileName

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [FileSize] = @SizeMB
    ,[UsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

END

SELECT [EndFileSize] = @SizeMB ,[EndUsedSpace] = @UsedMB ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a