January 9, 2014 at 7:57 am
Hi
I have a problem on my hands, we have an instance with sql 2008 r2, a database of 2TB with one table of about 500gb, on a drive with only 70gb of free space, no partitions in there on a 24/7 environment
I need to rebuild the primary key since we run an archiving process that deleted many rows from that table, but as you now SQL is no good at all at recovering space
If I try to run a rebuild of that index I will end up consuming all the space on that drive
If I try to run a reorganize it will consume the log drive
If I try to partition the table, which is something I really want to do, that would mean a considerable downtime that I can't afford
What other options do I have?
Thanks in advance
January 9, 2014 at 8:12 am
what version of 2008, standard or enterprise?
Whats the recovery mode of the database?
I presume the PK is the clustered index?
You could reorganise and backup the log up at intervals
you could sort in tempdb
you could rebuild online
---------------------------------------------------------------------
January 9, 2014 at 8:23 am
2008 enterprise, database is in simple mode, and yes that is the clustered index
January 9, 2014 at 8:59 am
well then you have online rebuild with sort_in_tempdb open to you, (as long as no LOB data) and even multi threaded. But that would still be resource intensive and I would go for a reorganise first.
Log would not be a problem in simple mode for either option as they are discreet transactions that can be committed and removed from the log by each checkpoint.
Check the amount of fragmentation before to see just how bad it is, check again afterwards. If it is reasonable and performance is OK then leave it, otherwise then consider a rebuild online.
If you reorganise you will need to run update statistics afterwards as well.
---------------------------------------------------------------------
January 9, 2014 at 9:36 am
It seems to me that you can drop and recreate the PK / Index in question, which will free the space of the existing index and make it available for the rebuilt index.
Thanks
John.
January 9, 2014 at 9:41 am
I'd be loathe to drop a PK constraint on an active system and the OP specified this is a 24/7 system so downtime is an issue.
---------------------------------------------------------------------
January 9, 2014 at 9:48 am
george sibbald (1/9/2014)
I'd be loathe to drop a PK constraint on an active system and the OP specified this is a 24/7 system so downtime is an issue.
Agreed; if there is no way to gain exclusive control over the table for ~1-2 hours there will be no way to drop and recreate without risking duplicate keys. My personal preference is to grab single user mode for such an operation.
The OP mentioned disk space constraints; he may have a catch-22 unless the free space in the existing file groups plus the free on-disk space is greater than the size of the PK.
Thanks
John.
January 9, 2014 at 10:01 am
on a test environment I created a copy of that database, after 12 hours trying to drop the index it failed due to space on the log file, it grew up to 80 gb
Alreadsy tried the sort_ontempdb, and I am almost sure that think dont work because the index still used up all the inner space of the database and the remaining space on the drive, I am trying with the very old and not trusty INDEXDEFRAG
the downtime can be really large since we are talking of a table with nearly 1 billion rows
January 9, 2014 at 10:04 am
ricardo_chicas (1/9/2014)
on a test environment I created a copy of that database, after 12 hours trying to drop the index it failed due to space on the log file, it grew up to 80 gbAlreadsy tried the sort_ontempdb, and I am almost sure that think dont work because the index still used up all the inner space of the database and the remaining space on the drive, I am trying with the very old and not trusty INDEXDEFRAG
presume you mean alter index reorganise, perfectly trusty.
---------------------------------------------------------------------
January 9, 2014 at 10:07 am
No, I mean INDEXDEFRAG command, not even its creator recommend it but I do not see many options available right now
The alter index organize consumes log space when the tables are big enough
January 9, 2014 at 10:09 am
ricardo_chicas (1/9/2014)
on a test environment I created a copy of that database, after 12 hours trying to drop the index it failed due to space on the log file, it grew up to 80 gbAlreadsy tried the sort_ontempdb, and I am almost sure that think dont work because the index still used up all the inner space of the database and the remaining space on the drive, I am trying with the very old and not trusty INDEXDEFRAG
the downtime can be really large since we are talking of a table with nearly 1 billion rows
1. How large is the table in data space size?
2. How large is the PK in index space size?
3. How much space is free inside the present file groups?
4. How much space is free on disk?
5. Is there a possibility of opening a new file group on other disk(s) that would be large enough to accommodate this table (i.e., 500GB)?
6. What is the fragmentation level of the index according to the fragmentation DMV? If you don't know here is a query:
DECLARE @db_id SMALLINT;
SET @db_id = DB_ID(N'your DB');
SELECT
OBJECT_NAME(DMV.object_ID) as tablename
,SI.NAME as Indexname
,DMV.index_type_desc
,DMV.avg_fragmentation_in_percent
,DMV.fragment_count
,DMV.avg_fragment_size_in_pages
,DMV.page_count
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , 'LIMITED') as DMV
LEFT OUTER JOIN SYS.INDEXES AS SI
ON DMV.OBJECT_ID = SI.OBJECT_ID
AND DMV.INDEX_ID = SI.INDEX_ID
where SI.name is not null
and dm_db_index_physical_stats.object_id = object_id('your table')
order by DMV.avg_fragmentation_in_percent desc;
This will help us a little...
January 9, 2014 at 10:10 am
Sorry - Duplicate [snip].
January 9, 2014 at 10:20 am
surprised indexdefrag even works
how much log does the reorganise use? the DB should at least checkpoint when the log gets to 70% full. Does dbcc opentran show the reorganise as the oldest transaction? Maybe too much log is produced too quickly between checkpoints
Have you tried issuing checkpoints manually or via a SQLAgent threshold alert more frequently as the log grows?
---------------------------------------------------------------------
January 9, 2014 at 10:50 am
will try the checkpoints approach once the other command is done, when I tried the reorganize I checked opentran and saw that transaction running consuming the whole available space
January 10, 2014 at 3:40 am
I had this situation a few years back on a database (2005 standard) that has now reduced dramatically in size but at its peak was over a terabyte, mostly consisting of one table with over a billion rows. all I could do on that was run reorganise. The log for that is 200GB but not sure now how much of that was down to the reindexing or large overnight loads that were run.
So, you probably need more disk space for log, or as the work already done by reorganise is not lost when you kill it, just run it for as long as you can with the log available and do it in 'chunks'. Once you have the fragmentation under control you may be able to cope with your current size restrictions.
good luck.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply