Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Killed clustered index creation, running out of space Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 10:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:09 PM
Points: 69, Visits: 256
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...
Post #1411264
Posted Thursday, January 24, 2013 11:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1411280
Posted Thursday, January 24, 2013 11:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:09 PM
Points: 69, Visits: 256
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.
Post #1411283
Posted Thursday, January 24, 2013 2:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1411339
Posted Thursday, January 24, 2013 2:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 2,330, Visits: 3,509
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 .

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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1411356
Posted Thursday, January 24, 2013 3:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:09 PM
Points: 69, Visits: 256
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!!
Post #1411376
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse