Enterprise Edition DB not growing despite available disk space

Question

Post reply

Enterprise Edition DB not growing despite available disk space

chrisfradenburg

SSCrazy Eights

Points: 9592
More actions
November 14, 2016 at 12:04 pm

#322986

I got the, "Could not allocate space for object," error on one of our larger databases while it was being reindexed this weekend. To sum up the relevant points:
1) This is Enterprise Edition in an AlwaysOn setup.
2) Monitoring shows that both servers had 3.5TB free when the error happened
3) The DB in question has two files in the file group (primary, the only file group), the first is 25MB and has autogrow disabled. The other grows by 5000MB increments with unlimited growth and had an autogrowth event a couple hours after it failed to grow with the reindex
4) There are no IO errors in either the Application or System log on the primary server around this time
5) Windows is 2012R2 and SQL is 2014 SP1 CU6
6) The database is 2TB and the table in question is 488GB and the index is 364GB.
Is there any reason that a database like this wouldn't be able to autogrow during a reindex?
November 23, 2016 at 9:01 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What are the settings for growth of the data and log files? What is the full error message you received?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

1) Was the error message you posted the ENTIRE message? If not please provide.

2) What objects exist on the 25MB file?

3) What is tempdb configuration on both servers?

4) Where there any non-IO errors around the time of the failure?

5) What do you use to do your index defrag operations? If Ola's stuff please provide details from the logging it does. If not, why aren't you using his stuff? 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 3

Lynn Pettis (11/14/2016)
What are the settings for growth of the data and log files? What is the full error message you received?

type_descsize max_sizegrowthis_percent_growth

ROWS3200 -1 0 0

LOG 19185384 19200000640000

ROWS259200000 -1 6400000

Both data files are in the primary file group. The full error message is:

Could not allocate space for object 'dbo.<table>'.'<nonclustered_index>' in database '<database_name>' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

TheSQLGuru (11/14/2016)
1) Was the error message you posted the ENTIRE message? If not please provide.
2) What objects exist on the 25MB file?
3) What is tempdb configuration on both servers?
4) Where there any non-IO errors around the time of the failure?
5) What do you use to do your index defrag operations? If Ola's stuff please provide details from the logging it does. If not, why aren't you using his stuff? 🙂

Can you let me know how to see what objects are in a specific file as opposed to a file group?

TempDB is the same on both servers, 8 33GB files that were almost entirely empty at the last monitoring pull before the error.

There were no non-IO errors as well.

We don't use Ola's because when we implemented his solution SQL Fool's reindex scripts allowed more fine grained control. It started the reorg operation on this index at 3:12:38 and errored at 3:33:40 with the error above.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

http://stackoverflow.com/questions/20129001/see-what-data-is-in-what-sql-server-data-file

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

That shows which objects are in which file group (data_space_id) but since both data files are in the same group it didn't show which objects are in which file.

After some more poking I found sys.dm_db_datbase_page_allocations and the index in question has 48 pages in the 25MB file and 37255814 pages in the larger file.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

I will assume the problem is because some of that index is on the locked-down file. I don't know if the shrink-file-and-empty-it-process can be used to get all of your pieces off of that file. If not the only way I know of is to move everything to a new file group.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 7

We can't empty that file because it has the system objects. That may move the user objects but I'm not sure.

I'm also curious as to why it wouldn't just allocate pages in the other file since it's clearly doing that anyway. A subsequent run of the reorg fails with the same error so it's something related to that and can be reproduced.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

chrisfradenburg (11/15/2016)
We can't empty that file because it has the system objects. That may move the user objects but I'm not sure.
I'm also curious as to why it wouldn't just allocate pages in the other file since it's clearly doing that anyway. A subsequent run of the reorg fails with the same error so it's something related to that and can be reproduced.

Given that it is repeatable you should present this to Microsoft to see if it is a bug. But it could be by design under the covers, such as some system allocation page or row about this index and the no-growth issue is preventing that thing from happening.

Why don't you add in a 25MB growth for the file and cap it at 75MB size and see if what "needs" to happen can happen without trying to bloat that 25MB file up to a bajillion GB? If it really is just a small system thing (or some other user index thing like a root page activity or something) then you should be good to go. If it immediately slams the 75MB full now you can go use DBCC PAGE or other mechanisms to check out what has happened on that file and get more info. Or call Microsoft at that point ...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Or, create a new file group and file and drop and recreate the nonclustered index(es) in the new file group.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Lynn Pettis (11/15/2016)
Or, create a new file group and file and drop and recreate the nonclustered index(es) in the new file group.

I already suggested that as a solution but with 364GB I imagine it will be a rather painful exercise. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

TheSQLGuru (11/15/2016)
Lynn Pettis (11/15/2016)
Or, create a new file group and file and drop and recreate the nonclustered index(es) in the new file group.
I already suggested that as a solution but with 364GB I imagine it will be a rather painful exercise. 🙂

True, but it gets it off the space limited file.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 12

I've opened a case with Microsoft and I'll update this with what they come up with.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 13

A reorg can only move pages withing the file that they're in. I would need to do a rebuild or drop and create to handle this. We're going to meet with the owners of this database and talk about whether or not it's worth adding a second file group and work on moving things over to avoid this in the future or if we should do a drop and then shrink the file so no new pages can be allocated before recreating but I expect that to be an even more painful exercise to get all non-system objects out of that file.

Thanks for your help.