March 14, 2012 at 6:51 am
Ok... never thought it would have happened, but dropping and trying to re-create a clustered index on a very large table caused the process to fail after 1.5 days due to lack of disk space. (The order of fields in the cluster were not in line with the queries run against the table).
We started with a 1.2Tbyte data file, which grew to 1.9Tbytes and filled the disk. The only item on this disk is the data file... TempDB and the log file have their own disks.
I wanted to see if anyone had any recommendations before I take a drastic method and delete, then restore the database from a backup done over the weekend. Based on a previous incident, the restore takes 2.5 days, but that was then the datafile was 700 GBytes in size.
The system is setup as follows:
Microsoft Server Enterprise 2008 (32-bit)
Service Pack 2
Intel Xeon CPU
E5470 @ 2.23 Ghz
3.33 Ghz, 32.0 GB of RAM
Server Server 2005 (32-bit)
Standard Edition
Service Pack 3
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 14, 2012 at 7:48 am
If you have enough space in the tempdb drive, you could use SORT_IN_TEMPDB.
Another option is adding temporarily a new data file in the same filegroup in a different drive with sufficient disk space.
-- Gianluca Sartori
March 14, 2012 at 7:51 am
The system is setup as follows:
Microsoft Server Enterprise 2008 (32-bit)
Service Pack 2
Intel Xeon CPU
E5470 @ 2.23 Ghz
3.33 Ghz, 32.0 GB of RAM
Server Server 2005 (32-bit)
Standard Edition
Service Pack 3
Now I see that you are using SQL Server 2005 x86 on a server with 32 GB of RAM. Wouldn't a 64 bit version perform better?
-- Gianluca Sartori
March 14, 2012 at 8:09 am
Gianluca Sartori (3/14/2012)
If you have enough space in the tempdb drive, you could use SORT_IN_TEMPDB.Another option is adding temporarily a new data file in the same filegroup in a different drive with sufficient disk space.
Gianluca,
Thanks for the response/suggestions! 🙂
Now you tell me about the sort_in_tempDB!!!!! LOL. Yes, in hindsight, that is how this should have been setup! TempDB has it's own 2TB drive, so no problems there.
Regarding the new data file in the same filegroup, that is what I am looking at right now. I might see if I can move most of the table indexes onto a different drive to free up space.
Also, I need to delete older data from this database, just need to complete some syncs with other databases first. This will free up quite a bit of room (removing 1 year of data).
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply