March 16, 2016 at 8:11 am
I have a new error today that i cant seem to find much information on..
I am trying to relase space from one of our databases and the following pops up:
Cannot create row size 8190 which is greater than the allowable maximum row size 8060. DBCC Shrinkfile: HEAP Page 1:11111 cannot be moved.
I have tracked the table down that the page belongs to but not sure what to do with it?
March 16, 2016 at 8:17 am
SQLAssAS (3/16/2016)
I have a new error today that i cant seem to find much information on..I am trying to relase space from one of our databases and the following pops up:
Cannot create row size 8190 which is greater than the allowable maximum row size 8060. DBCC Shrinkfile: HEAP Page 1:11111 cannot be moved.
I have tracked the table down that the page belongs to but not sure what to do with it?
How did you get this error? What code were you running? Have you checked your database? I am thinking there could be some corruption happening here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 16, 2016 at 8:26 am
Hello,
Just a shrink command..
DBCC SHRINKFILE (N'DATABASE' , 100621)
GO
I just ran a checkDB on the database and no errors popped up so corruption shouldnt be an issue
March 16, 2016 at 8:27 am
As Sean referred to, please run DBCC CHECKDB and post the results.
Also, please post the structure of the table in question.
March 16, 2016 at 8:40 am
SQLAssAS (3/16/2016)
Hello,Just a shrink command..
DBCC SHRINKFILE (N'DATABASE' , 100621)
GO
I just ran a checkDB on the database and no errors popped up so corruption shouldnt be an issue
Hehe just a shrink command. 😉 Do you do this frequently? Shrinking is incredibly expensive and causes all sorts of other problems.
Unless you can post some details other than an error message this is nearly impossible to help. Table definition including indexes, approximate row count etc. Without something to use a guide this is just trying to interpret what might have caused an error to occur that is not your run of the mill type of issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 16, 2016 at 8:40 am
Use DBCC PAGE to get the object I'd, then it is easy to find the table
😎
March 16, 2016 at 10:38 am
Hi,
We dont do this frequently no I know its not good practise but this is a one off to recover space after a big delete.
DBCC checkdb..
for this particular table...
DBCC results for 'Table_Name'.
There are 167210 rows in 167210 pages for object 'Table_Name'
over all
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Database_name'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
no concerns there.
I Have just tried to create a clustered index on the table too and recieved this error:
Cannot create a row size 8061 which is greater than the allowable maximum row size of 8060. Warning: The maximum length of the row exceeds the permissible limit of 8060. FOr some combination of large values, the insert /update operation will fail.
The table has 167K rows, but 384 columns and they are all varchar(MAX)! I think thats the issue!! I didnt create it!
Luckily i have just been told the table is not actually required or used so I can drop it. I will restore the backup to a test box and continue to play though! Now the table is removed, the shrink works....
March 16, 2016 at 11:51 am
LOL I suppose dropping the table is the easiest way to solve the issue. Glad you were able to find out you could remove it. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply