Error while creating clustered index
-
Hi Experts,
While trying to create a clustered idx on a HEAP Table, we are seeing below error. How to avoid this error. This Table is originally a HEAP and now we wanted to remove fragmentation (~25Gb), so tried creating clustered on this table using SSMS. It is throwing Timeout error. How to fix this? Kindly help.
Thanks,
Sam
-
This was removed by the editor as SPAM
-
Script the statement and execute it from a query window instead of the GUI. This will avoid the timeout error - but I am guessing based on your other question that this will fail because you don't have enough space available.
Adding a clustered index will require all non-clustered indexes to be rebuilt also - and will require space in the data file, tempdb (if you use sort_in_tempdb = on), and transaction log space.
Based on your other message - you need to allocate more space to the drive containing your transaction log before this will complete successfully.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks Jeff.
-
when you script it (as jeff correctly suggested), run activity monitor or sp_who2 and see if the process is being blocked
not wanting to teach anyone to suck eggs, but your SPID is in brackets next to your username at the bottom right of management studio... just try and find anything blocking that spid and see if you can kill the process that is blocking you
if you want to see what the blocking process is doing then you can run
DBCC inputbuffer(77) ---assuming 77 is the blocking process
MVDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply