SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Killed clustered index creation, running out of space


Killed clustered index creation, running out of space

Author
Message
Marius.D
Marius.D
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 289
Well, it's not full, sorry, it just doesn't have enough room available (81 out of 84GB). It is not set to autogrow, it takes up 99% of a disk partition, logfile is on different partition...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91925 Visits: 45285
Marius.D (1/24/2013)
Well, it's not full, sorry, it just doesn't have enough room available (81 out of 84GB).


That looks pretty full to me.

I think, before worrying about indexes, you might want to address the storage issue, get more drives, expand storage.

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


Marius.D
Marius.D
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 289
So you're saying since I can't use tempdb for sorting because I only have 81GB available, I should take care of that first..
Well, this is a large table, 99% of the tables would fit into Tempdb.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91925 Visits: 45285
You have inadequate space on the TempDB drive and the user database drive to rebuild a clustered index (yes, rebuilding requires the same free space as creating it), that's a pretty clear indication that you are low on disk space, when regular maintenance will run a drive out of space.

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


ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8384 Visits: 7163
An extraordinarily large table (which I'll abbreviate "xlt" for convenience) can indeed cause unusual issues.

If you use tempdb, it needs (lots of) extra disk space, which basically can't be freed while SQL is running.

If you don't use tempdb, you might not have any good choice except to test for avail. space and explicitly grow the db file(s) containing the xlt before doing the rebuild if necessary. You'll want to make sure IFI is set on for that if at all possible (hopefully it's already on all the time anyway).

But then it will be extremely difficult to shrink the db to regain any of that disk space either, as that can fragment that xlt you just rebuilt.

Ouch! The disk space is basically gone either way Sad.

Given that, I prefer to increase tempdb to the point where it can handle the xlt rebuild. That way, if any new xlt(s) are ever added, the tempdb space handles rebuilding any of them (you just can't rebuild more than one of the xlt's at the same time). And the rebuild results are often better, resulting in a less fragmented xlt, which was the point of the rebuild in the first place.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Marius.D
Marius.D
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 289
I have already asked for more disk space last week...
There are also several unused tables that I discovered, and I am fighting now to have them removed. Might not happen.
I have also been deleting unused indexes.

In any case, I would not shrink the mdf file.

But, tempdb is a problem...

Thanks again!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search