Error while trying to shrink a data file

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  September 16, 2023 at 1:54 pm

  Go to Answer

  #4287801

  Hi All,

  I am trying shrink a file which is of 1TB to 500GB. Used space is 250GB.

  When I try to shrink the data file, it throws me below error.

  Any idea why it is throwing that error? How to troubleshoot this issue and what are the options available if I had to shrink this data file?

  DBCC SHRINKFILE(db1_dat,512000);

  Error Message:

  Msg 511, Level 16, State 1, Line 62

  Cannot create a row of size 8193 which is greater than the allowable maximum row size of 8060.

  DBCC SHRINKFILE: Heap page 1:134083999 could not be moved.

  The statement has been terminated.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Regards,

  Sam

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  September 17, 2023 at 5:13 am

  #4288092

  The error is telling me that you have a table that might be in a little trouble and some column widths need to be adjusted either for width or maybe changed to a LOB datatype.

  --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)
  

• andreas.kreuzberg

  SSCertifiable

  Points: 6191

  More actions

  September 19, 2023 at 5:13 am

  Answer

  #4289323

  Hello,

  have you tried to shrink in smaler trunks, like:

  DBCC SHRINKFILE(db1_dat,921600);

  We had sometime trouble to shrink, if the step was to big. We used smaler "steps", and every thing was fine.

  Kind regards and good luck,

  Andreas