Unable to shrink Database

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  January 16, 2018 at 8:12 am

  #1975995

  jjimenez-1040959 - Tuesday, January 16, 2018 8:04 AM

  Jeff Moden - Tuesday, January 16, 2018 7:32 AM

  jjimenez-1040959 - Tuesday, January 16, 2018 5:10 AM

  In SSMS
  -Right click / properties on the database
  -In files section check the initial size and set it to a lower value
  -Click Ok

  Now the shrink file/database will work

  I always appreciate an answer even if it's a bit late.  Just so you know, your responding to a post that's over 9 years old. 😉

  I know, but I find the post today with the same problem.
  And was a little confused to see everybody telling "no you should not shrink database! Its bad ! gnagnagna " but without answering to the real question.
  I think that it good for the comunity to share when a solution is found.

  Bye

  Yep... very much appreciated and that's why I said what I said.  Also, you forgot one of the important parts for the solution. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• RandomStream

  Hall of Fame

  Points: 3735

  More actions

  January 16, 2018 at 10:15 am

  #1976009

  Before shrinking, find out how much free space each data file has. Here is a query (with slight modification) from Glenn Berry to help.

  If there is only 200GB free space and you are trying to shrink by 250GB, SHRINKFILE will not work. So it is important to find out how much free space you have. Also, if the data file is too fragmented, it will not work either even if there is free space.
  
USE <your_700GB_db_name>
GO
SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size (MB)],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Free Space (MB)],
CAST (CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) * 100 / CAST((f.size/128.0) AS DECIMAL(15,2)) AS DECIMAL(5,2)) AS [% Free],
f.[File_ID], fg.name AS [Filegroup], f.is_percent_growth As [Growth By %], f.growth/128 AS [Growth Size (MB)], fg.is_default, fg.is_read_only
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.filegroups AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id
ORDER BY f.[file_id] OPTION (RECOMPILE);