Sql server table can be queried but not updated

Question

i have a table which was always updatable before, but then suddenly i can no longer update the any of the columns in the table. i can still query the whole table and the results come back very fast, but the moment i try to update a column in the table, the update query simply stalls and does nothing.

i tried using

select req_transactionUOW
from master..syslockinfo
where req_spid = -2

to see if some orphaned transaction was locking the table, but it returns no results.

i can't seems to find signs of my table being locked, but simply cannot update it. any clues as to how to fix the table or whatever state it is in?

Answer 1

I'm guessing this isn't a permissions issue as you're not getting an error.

So the closest I have had to this before is when the indexes on the table have become corrupt. Have you tried dropping the indexes and recreating them? Try one by one at first.

Answer 2

Could you please issue this query:

SELECT  COUNT(*)
FROM    mytable WITH (UPDLOCK, READPAST)

which will skip the locked records and make sure it returns the same number of records as

SELECT  COUNT(*)
FROM    mytable

You may need to repeat it with every index on the table forced, to make sure that no index resources is locked as well.

Answer 3

If you suspect locking, one of the first things I would do would be to run sp_lock. It will give you a list of all of the current locks held. You can use the DB_NAME and OBJECT_NAME functions to get the names that correspond to the dbid and ObjId columns.

Answer 4

Have you got any triggers on the table? If so it could be that the trigger is failing so preventing the update.

Answer 5

When you say "times out", does it hit the client timeout? For example, the default .net command timeout is 30 seconds. I would suggest increasing this to a very large value or running the update in SQL tools (by default no timeout set).

Other than that, an update will finish at some point or error and rollback: are you leaving enough time?

There is also the blocking, last index rebuild, last statistics update, triggers, accidental cross join, MDF or LDF file growth, poor IO, OS paging... etc. And have you restarted the SQL instance or server to remove environmental issues and kill all other connections?

There simply isn't enough information to make a judgement right now sorry.

Answer 6

Can you update other tables? If not (or anyways, if you like) you could check if the transaction log is full (if you use the full recovery model)/the partition your transaction log resides on is full. I think if SQL Server is unable to write to the transaction log you would/could experience this behaviour.

DBCC would be your friend: DBCC SQLPERF(LOGSPACE) shows you, how much (in percent) of your log is used. If it is (close to) 100% this might be your issue.

Just my two pennies worth.