June 3, 2009 at 12:28 am
Very odd have a relatively large table that needs regular index maintenance.
It has been moved to a sql 2005 x64 Cluster (from SQL 2005 x32 stand alone)
The index itself is around 3.5gb (quite a large table)
When I run
ALTER INDEX
The transaction log file immediately uses up all available disk space 15GB! (tempdb is on different Drive and not affected)
Anyone seen this before or any ideas on how to troubleshoot?
June 3, 2009 at 12:33 am
Index rebuild consumes a lot of Transaction logs. So while rebuilding index i set my database to simple recovery model and after completion of process i set it to recovery model full. I found this practice helpful. I mentioned this on http://blog.dbdigger.com/2009/05/change-recovery-model-of-sql-srever.html
DBDigger Microsoft Data Platform Consultancy.
June 3, 2009 at 12:42 am
Thanks for your input but:
That will break the LSN Chain making the following TL backups fail until the next full backup
And the transaction log growth should not be 5 times the size of the index rebuild especially as its supposed to be happening in the Tempdb...
June 4, 2009 at 4:53 am
How big is the table? you said the index is 3GB.
Is this a clustered index, or does the table have a clustered index on.
If so then your alter index command will rebuild all indexes on the table
June 4, 2009 at 5:07 am
Yep its a clustered index and the 3.5gb covers total size of all indexes on the table
And yep it needs to rebuild all
June 4, 2009 at 5:20 am
Not sure then.
I normally find if my table is zGB then doing an online reindex usually requires z+10-20% of space.
Perhaps do a profile during it to ensure no other large transactions are running at the same time.
If its in full mode you could force a few transaction log backups during it to stop it growing so big - doesnt really fix the underlying problem though.
June 4, 2009 at 5:44 am
Joy ,
Let me clear your concept here .
When you use the sort in tempdb option the intermediate sorting is done in the tempdb .This improves sorting speed but does not gurantee the the less space will be used .Infact many a times i have seen more space is being used inside tempdb depending on the nature of index .
Part 2 : the reason your log file is increasing is because of the database recovery model .As its set to FULL every transaction has to be logged to meet the situation if it has to rollback .So you need to have more space for your log to grow .
you can also go through : http://technet.microsoft.com/en-us/library/ms188281.aspx
Hope this helps .I also had some good material proving when the database size will be increasing (and when not) even if the SORT IN TEMPDB is set to ON .But as i have relocated to a new Co I am not able to find it ..
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 4, 2009 at 5:53 am
A Joy (6/3/2009)
Very odd have a relatively large table that needs regular index maintenance.It has been moved to a sql 2005 x64 Cluster (from SQL 2005 x32 stand alone)
The index itself is around 3.5gb (quite a large table)
When I run
ALTER INDEX on
REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON)
The transaction log file immediately uses up all available disk space 15GB! (tempdb is on different Drive and not affected)
Anyone seen this before or any ideas on how to troubleshoot?
Did you think of changing the recovery model to Bulk logged, this will minimally log the transactions? and do you really have to rebuild the indexes or would it be suffice to re-organize them?
June 4, 2009 at 6:11 am
the leaf node of the clustered index is the actual data, so when you rebuild the clustered index you are effectively moving the table data around. Hence the large amount of log. I suggest you try switching to bulk logged move before the rebuild.
A reorg would produce a lot of log as well, but does allow you the opportunity to backup the log during the process so you might be able to get some control with that. Set a performance alert to back up the log if it gets to 50% full and make sure you start with the log as empty as possible.
---------------------------------------------------------------------
June 4, 2009 at 7:05 am
Thanks for the input but I am trying to troubleshoot the issue
The transaction log file immediately uses up all available disk space, 15GB!
So no point setting alerts etc
Clustered index is the data (essentially), given, however the table is 5gb and the TL is jumping 15gb
Secondly the sort is set to be done in the TempDB according to microsoft
(during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area in tempdb)
This should all occur on the tempdb (ie no file growth of any kind on the database itself as there are only reads occuring) until the write back after the sort
The bulked logged solution is interesting (although theoretically shouldn't make any difference) and something I will try (and feedback) but doesn't resolve what appears to be aberrant behaviour, it was really how to investigate this that I was interested....
June 4, 2009 at 7:34 am
AShehzad (6/3/2009)
This may be an issue. In my case i have relatively small database so i perform a full backup before maintenance process each week.
If you set to simple, you need to take a full backup AFTER, so you get a new log chain started.
June 4, 2009 at 7:43 am
You should check the growth size setting on your log file to make sure it is not set to a very large value or percent. If it is, set it to a reasonable size.
This is very likely to be the source of your problem, since you say that the growth occurs immediately.
June 4, 2009 at 7:49 am
A Joy (6/4/2009)
This should all occur on the tempdb (ie no file growth of any kind on the database itself as there are only reads occuring) until the write back after the sort
the write back itself will produce log activity and its more than a 1:1 ratio of the amount of extents written
---------------------------------------------------------------------
June 4, 2009 at 8:07 am
george sibbald (6/4/2009)
A Joy (6/4/2009)
This should all occur on the tempdb (ie no file growth of any kind on the database itself as there are only reads occuring) until the write back after the sortthe write back itself will produce log activity and its more than a 1:1 ratio of the amount of extents written
Nope set to 100mb, good thing to check though
The write back will be more than 1:1 but not 3x the full size of the table and only once the sort has occured, this is happening immediately...
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply