Reindex failed primary file group not enough space

Question

Reindex failed primary file group not enough space

sqlfriend

SSC Guru

Points: 52794
More actions
October 29, 2011 at 10:39 am

#390717

Last night we have one maintence job failed during the reindex task. The error is there is not enough space on the primary file group.
I checked our primary file group- data file is set to restriction of 10 gb, 37% percent free now.
So I guess during the reindex process, it tried to use more than 10 gb, and because we set to 10 gb restrict, so it failed.
We usually have issues about transaction log file not enough space during reindex process at weekend, this is the first time we have problem with Data file. The only difference is we added a couple of domain tables in the database on Friday night for a release.
I can certainly increase the data file restrict size to a large one, for we have enough disk space, but since the other time execpt during reindex, there is still 37% free, I don't want to do it now.
My question is does reindex use transaction log file only, or it also increases the data file size?
For the situation now, what is the best solution to fix this?
Thanks much

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The reindex makes a 2nd copy of the index. Then once done swaps it into place. So while this is happening you need at least another 100% of the space of the orinigal object. I'm sure it's a little more than that but I don't know the exact number.

That being said, any way you can use sort in temp db. Maybe it would solve your immediate problem without growing the data file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Around 120% unless using Sort In TempDB, then it's just 100% plus metadata

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

When you say it's 100-120 % of original objects, you mean use the space in data file or log file.

The issue we had before is transaction log full during reindex. So I extended log file size.

This is the first time we have primary file group data file full. Not sure why this happens?

The only difference is this weekend we add a small domain table and a couple of constraints.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sqlfriends (10/31/2011)
When you say it's 100-120 % of original objects, you mean use the space in data file or log file.

Both.

100% the size of the index + log header overheads for log space (assuming full recovery), 120% space in the data file to do the sort and put the new index down.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

Thank you,

So now our data file currently allocated space is 10 gb, availble free space of 35%, backup file is 6.5 gb.

If it needs 120%, does it mean at least I need to allocate 4.5 gb space to the data file, total at least 14.5gb?

Also when you say sort in temp db, how can I accomplish that?

Currently I use out of box maintenance plan - reindex task, is there a place that says sort in tempdb?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

sqlfriends (10/31/2011)
If it needs 120%, does it mean at least I need to allocate 4.5 gb space to the data file, total at least 14.5gb?

Maybe. You haven't said how big the biggest index in the database is, so without knowing how big the biggest index is, I can't comment on 120% of the size of the largest index.

Currently I use out of box maintenance plan - reindex task, is there a place that says sort in tempdb?

I don't believe there is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Actually there's an easy way out of this.

Maint. Plans always reindex everything no matter what (needed or not). This is usually a big waste of time and ressources.

I use this instead... which you can call in the maint. plan instead of the current code (you'll need to add a step to run sql statement instead of the current step to reindex. I'm no expert in ssis so I can't give clearer directions than that).

http://sqlfool.com/2011/06/index-defrag-script-v4-1

sqlfriend SSC Guru Points: 52794 More actions · Answer 8

Thanks all.

First, I see the script is so long, I will have to study it a little bit later before I use it .

Second, I checked in the maintenance plan we do have the sort in tempdb checked in the reindex task.

How can I know how much space do all the indexes use in the database?

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

sqlfriends (10/31/2011)
Thanks all.
First, I see the script is so long, I will have to study it a little bit later before I use it .
Second, I checked in the maintenance plan we do have the sort in tempdb checked in the reindex task.
How can I know how much space do all the indexes use in the database?
Thanks

That's close to what you need.

http://jasonbrimhall.info/2010/05/19/tablespace-update/

The script I posted creates the script to run. To call it you just call an sp with 2-3 parameters.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Query sys.dm_db_index_physical_stats. Limited mode is good enough. See the page_count column

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 11

GilaMonster (10/31/2011)
sqlfriends (10/31/2011)
When you say it's 100-120 % of original objects, you mean use the space in data file or log file.
Both.
100% the size of the index + log header overheads for log space (assuming full recovery), 120% space in the data file to do the sort and put the new index down.

Thanks all.

First I use sp_spaceused and find out the index is 0.7 gb.

But my data file isallocated 10 gb space, and when not doing the reindex, it is 36% free.

Secondly, in maintence plan I see we have already checked the check box: sort in tempdb.

But our reindex task still fails.

So how much space more I should allocate to the data file?

THanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Is your largest table under 0.7GB?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52794 More actions · Answer 13

Thanks,

I found in the error message, when reindexing, the task failed at one of the biggest table we have.

The table is 4 gb. and index is 0.6 gb.

It seems this is the table that used most of the space of our database, it is a snapshot table whenever a process happened.

So based on the size and the entire size of the database mentioned in above post, how much more space I need to assign to the database data file?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

sqlfriends (11/6/2011)
So based on the size and the entire size of the database mentioned in above post, how much more space I need to assign to the database data file?

I've already answered that, in this thread, at least twice.

By table = 4GB, you mean the clustered index is 4GB in size?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Reindex failed primary file group not enough space

Cookies on SQLServerCentral