Error while shrinking a data file

Question

Error while shrinking a data file

Sam 1,476

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

Anonymous

2023-09-18T09:24:26.0266667+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.

Accepted answer

1 additional answer

Your answer

Anonymous

2023-09-18T09:24:26.0266667+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

This is error is somewhat unexpected. I though shrink was only moving entire pages around, but it seems that I was wrong.

I'm also inclined to think that it is a bug that you get this error, but I would not expect Microsoft to fix it. So it is time for a plan B.

You first need to determine which table that is on this page. If you are on SQL 2019 or later, run this query:

SELECT pi.object_id, s.name, o.name
FROM   sys.dm_db_page_info(db_id('YourDB'), 1, 134083999, DEFAULT) pi
JOIN   sys.all_objects o ON pi.object_id = o.object_id
JOIN   sys.schemas s ON o.schema_id = s.schema_id

On older versions run

DBCC TRACEON (3604)
DECLARE @dbid int = db_id('YourDB')
DBCC PAGE(@dbid, 1,134083999)

And then look for ObjectID in the output and translate to a table name,

Once you have the table, verify that this is a wide table where the total column size can exceed 8060. Then you will need to decide what to do with it. Maybe copy it to another database with SELECT INTO, then truncate it the actual database, proceed with the shrink and copy data back. I would try this on a backup of the database first.

Sam 1,476 Reputation points

2023-09-16T18:20:46.99+00:00

We are on sql 2019 this particular instance. Sir, is there way to get the total columnsize or is it, looking at the column and datatype size? or using LEN(columnname)? little confused.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-09-16T18:30:41.3+00:00
What you should look at is the possible row size. Here is an example:

CREATE TABLE widetbl ( a varchar(8000), b varchar(8000), c varchar(8000) ) INSERT widetbl (a, b, c) VALUES ('This goes fine', 'This goes fine', 'This goes fine')

The possible row size is way over 8060, but the actual data is way smaller.

Now look at:

INSERT widetbl(a, b, c) VALUES(replicate('a', 6000), replicate('b', 6000), replicate('c', 6000))

Here the row size exceeds 8060, but yet the INSERT succeeds. This is because SQL Server uses overflow pages. What is happening with your table I don't know, but looks as if it's attempting to squeeze data from an overflow page into the base page. Or something like that. But the exact reason is not of interest. What I want you to do is to look at the table definition to see it is possible to have a row size > 8060. This is only to make sure that we are looking at a table that causes the problem.

Answer 2

Attempting the data file in the SQL server indicates that there are one or more rows in the data file with a size that will exceed the maximum row in the SQL server.

Checking and updating rows where you need resolution.Having rows for maximum size,

SELECT

You also need to review the structure of your table and the data types of columns involved.
Migration of data where there is a requirement of migrating data from the old table to the new table.
Shrinking the data file: try shrinking files into small increments.

Another option is to use a third-party tool to shrink the data file. There are a number of different tools available, and they typically offer a variety of features, such as the ability to shrink the data file without moving any data or to shrink the data file online without downtime.

Share via

Error while shrinking a data file

1 additional answer

Your answer