Skip to main content
Database Administrators

Return to Answer

added 71 characters in body
Source Link
AMtwo
AMtwo
  • 16.3k
  • 1
  • 33
  • 64

Please read my post on how to shrink a database, which I'll summarize here:

Look at your data file size

Look at the size of all files in your filegroup. You want all files within the group to be evenly sized, and you want to allow space for growth, index maintenance, etc. It makes more sense to err on the side of leaving the database a little too large than shrinking it to be too small. Personally, I'd account for at least 6 months growth in my target size.

SELECT
     LogicalName = dbf.name
    ,FileType = dbf.type_desc
    ,FilegroupName = fg.name
    ,PhysicalFileLocation = dbf.physical_name
    ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
    ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - ((dbf.size/128.0)
               - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT) /128.0))
    ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;

Consider the side effects

It sounds like you've already done this, and are OK with having to perform index maintenance following your shrink. Make sure you plan enough time to shrink and perform index maintenance during your maintenance window. It might be faster & easier to use the maintenance window to migrate into a fresh database, or rebuild all indexes into a new filegroup, rather than shrinking the existing one.

Shrink your database

Always use `SHRINKFILE` and never `SHRINKDATABASE`. Use the info you determined in step 1 to build your `SHRINKFILE` statement(s). 
USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Review fragmentation & reorganize indexes

If you have a regular maintenance job, just kick off that job and have it do it's magic. It's going to take a much longer time than normal, because everything will be really fragmented. You'll generate a lot more transaction log than usual, so you will have larger transaction log backups, and you will also see the effects in any log shipping, Availability Groups, mirroring, etc.

Please read my post on how to shrink a database, which I'll summarize here:

Look at your data file size

Look at the size of all files in your filegroup. You want all files within the group to be evenly sized, and you want to allow space for growth, index maintenance, etc. It makes more sense to err on the side of leaving the database a little too large than shrinking it to be too small.

SELECT
     LogicalName = dbf.name
    ,FileType = dbf.type_desc
    ,FilegroupName = fg.name
    ,PhysicalFileLocation = dbf.physical_name
    ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
    ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - ((dbf.size/128.0)
               - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT) /128.0))
    ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;

Consider the side effects

It sounds like you've already done this, and are OK with having to perform index maintenance following your shrink. Make sure you plan enough time to shrink and perform index maintenance during your maintenance window. It might be faster & easier to use the maintenance window to migrate into a fresh database, or rebuild all indexes into a new filegroup, rather than shrinking the existing one.

Shrink your database

Always use `SHRINKFILE` and never `SHRINKDATABASE`. Use the info you determined in step 1 to build your `SHRINKFILE` statement(s). 
USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Review fragmentation & reorganize indexes

If you have a regular maintenance job, just kick off that job and have it do it's magic. It's going to take a much longer time than normal, because everything will be really fragmented. You'll generate a lot more transaction log than usual, so you will have larger transaction log backups, and you will also see the effects in any log shipping, Availability Groups, mirroring, etc.

Please read my post on how to shrink a database, which I'll summarize here:

Look at your data file size

Look at the size of all files in your filegroup. You want all files within the group to be evenly sized, and you want to allow space for growth, index maintenance, etc. It makes more sense to err on the side of leaving the database a little too large than shrinking it to be too small. Personally, I'd account for at least 6 months growth in my target size.

SELECT
     LogicalName = dbf.name
    ,FileType = dbf.type_desc
    ,FilegroupName = fg.name
    ,PhysicalFileLocation = dbf.physical_name
    ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
    ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - ((dbf.size/128.0)
               - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT) /128.0))
    ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;

Consider the side effects

It sounds like you've already done this, and are OK with having to perform index maintenance following your shrink. Make sure you plan enough time to shrink and perform index maintenance during your maintenance window. It might be faster & easier to use the maintenance window to migrate into a fresh database, or rebuild all indexes into a new filegroup, rather than shrinking the existing one.

Shrink your database

Always use `SHRINKFILE` and never `SHRINKDATABASE`. Use the info you determined in step 1 to build your `SHRINKFILE` statement(s). 
USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Review fragmentation & reorganize indexes

If you have a regular maintenance job, just kick off that job and have it do it's magic. It's going to take a much longer time than normal, because everything will be really fragmented. You'll generate a lot more transaction log than usual, so you will have larger transaction log backups, and you will also see the effects in any log shipping, Availability Groups, mirroring, etc.

Source Link
AMtwo
AMtwo
  • 16.3k
  • 1
  • 33
  • 64

Please read my post on how to shrink a database, which I'll summarize here:

Look at your data file size

Look at the size of all files in your filegroup. You want all files within the group to be evenly sized, and you want to allow space for growth, index maintenance, etc. It makes more sense to err on the side of leaving the database a little too large than shrinking it to be too small.

SELECT
     LogicalName = dbf.name
    ,FileType = dbf.type_desc
    ,FilegroupName = fg.name
    ,PhysicalFileLocation = dbf.physical_name
    ,FileSizeMB = CONVERT(DECIMAL(10,2),dbf.size/128.0)
    ,UsedSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0 - ((dbf.size/128.0)
               - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT) /128.0))
    ,FreeSpaceMB = CONVERT(DECIMAL(10,2),dbf.size/128.0
           - CAST(FILEPROPERTY(dbf.name, 'SPACEUSED') AS INT)/128.0)
FROM sys.database_files dbf
LEFT JOIN sys.filegroups fg ON dbf.data_space_id = fg.data_space_id
ORDER BY dbf.type DESC, dbf.name;

Consider the side effects

It sounds like you've already done this, and are OK with having to perform index maintenance following your shrink. Make sure you plan enough time to shrink and perform index maintenance during your maintenance window. It might be faster & easier to use the maintenance window to migrate into a fresh database, or rebuild all indexes into a new filegroup, rather than shrinking the existing one.

Shrink your database

Always use `SHRINKFILE` and never `SHRINKDATABASE`. Use the info you determined in step 1 to build your `SHRINKFILE` statement(s). 
USE [DatabaseName];
DBCC SHRINKFILE(LogicalName, TargetSize);

Review fragmentation & reorganize indexes

If you have a regular maintenance job, just kick off that job and have it do it's magic. It's going to take a much longer time than normal, because everything will be really fragmented. You'll generate a lot more transaction log than usual, so you will have larger transaction log backups, and you will also see the effects in any log shipping, Availability Groups, mirroring, etc.