February 28, 2014 at 10:28 am
Last night I launched a clustered index rebuild on a huge table to put the index on a partition scheme. The rebuilt failed during a sort operation when the drive containing the filegroup filled up.
The clustered index appears to be there in SSMS, but is there a DMV or DBCC command that can tell me the extent of the corruption or damage?
February 28, 2014 at 11:32 am
What is the corruption or damage that you are referring to here ..
--
SQLBuddy
February 28, 2014 at 12:08 pm
If a rebuild of a clustered index was stopped before completion, I would think that might be a problem. How do I determine if there is a problem and if so, the extent of it?
February 28, 2014 at 12:13 pm
I'd suggest running DBCC CHECKDB. If that passes, you're OK in terms of corruption. That index may still be fragmented. Actually, pretty sure it will be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2014 at 12:28 pm
I did run dm_indexes_physical_stats on the table and the indexes were all either 0 or 0.45 percent fragmented. So assuming DBCC passes, is it probable that the only issue might be that the only some of the clustered index is in partitions?
February 28, 2014 at 12:35 pm
dan-572483 (2/28/2014)
I did run dm_indexes_physical_stats on the table and the indexes were all either 0 or 0.45 percent fragmented. So assuming DBCC passes, is it probable that the only issue might be that the only some of the clustered index is in partitions?
Not sure what you mean by "In partitions." Were you trying to partition a clustered index? It's likely that it simply rolled back when it didn't have enough space... unless it's corrupt. You won't get some of the index in a partition and some outside of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2014 at 1:25 pm
dan-572483 (2/28/2014)
I did run dm_indexes_physical_stats on the table and the indexes were all either 0 or 0.45 percent fragmented. So assuming DBCC passes, is it probable that the only issue might be that the only some of the clustered index is in partitions?
You can use DBCC CHECKDB to check the corruption.
But you should have a detailed logging for the index rebuild jobs. Try using the maintenance solution from http://ola.hallengren.com/ which gives you a detailed logging which which help you in troubleshooting the actual issue ..
--
SQLBuddy
February 28, 2014 at 3:04 pm
Try DBCC CHECKTABLE on the table that has the clustered index. No point in checking the entire DB when it's just the one table reporting the issue?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 3, 2014 at 8:43 am
MyDoggieJessie (2/28/2014)
Try DBCC CHECKTABLE on the table that has the clustered index. No point in checking the entire DB when it's just the one table reporting the issue?
+1
March 3, 2014 at 8:47 am
dan-572483 (2/28/2014)
If a rebuild of a clustered index was stopped before completion, I would think that might be a problem.
No. The rebuild will roll back completely leaving the index as it was before the rebuild started. A rebuild that fails because TempDB ran out of space will not corrupt your database. Nor will a rebuild that failed because the user database ran out of space. Nor will a rebuild which was stopped part way through.
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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply