SQLRNNR (8/3/2014)
Ok, that tells a tale of a much smaller table than what you were showing before.I bet there was blocking, or memory thrashing, or slow io, or any number of issues that could crop up due to resource use while trying to create that index.
You can use sp_who2 as a quick means to identify blocking on a query such as creating an index.
The total free space (from that query) is in MB.
I am running the creation of index and running SPwho2 as well
I notice there is 1 record has a profile as below :
SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID
172 SUSPENDED dgcLoginTST1-VW156 DB1 INSERT 152904108/04 14:31:57.Net SqlClient Data Provider 172 0
The database that I am creating the index is DB1 ..so it seems it is blocked by INSERT process ??
How we overcome that ?
thanks !