HELP!! Table not dropping

[pandatime]

Hi,

I have a table with three hexadecimal computed columns of type varbinary(8000). I did not create this table and I don't know why the author chose a column of varbinary(8000), when the max len on this column is only 16?

Anyways, I had some joins on these hexadecimal columns that were taking forver, so I indexed the hexadecimal columns. The indexes only took a few moments to create and sped up the query tremendously. I did get some errors to the effect that the length can't exceed 900 or something but didn't pay much attention to it because it seemed to work.

However, when I went to delete the indexes, it was taking a really looong time. So I decided to just drop the table. Now that's taking a really looong time. I've never seen this behavior before. Please help. The table has 500K rows.

Thank you

 

[gmmastros]

try dropping the indexes first.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

That was the original problem. The indexes were taking a REALLY long time to drop. I'll try again though.

Why would an index that only took moments to create take minutes to drop?

 

[pandatime]

I am wondering the the excessive column size is the problem. varbinary(8000)??? the max len is only 16. like i said, i didn't create this.

 

[gmmastros]

Is this a production database? It could be that someone is running a query that is using the computed column, and you are therefore getting locks on the table.

Try running (in a query window)
sp_who2

You should see a BlkBy column. If you see a number in the BlkBy column, then it's a blocking issue.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

Nevermind! An uncommitted transaction was the culprit.

 

[gmmastros]

That uncommitted transaction was blocking your drop statement.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom