Can't access single row in SQL Server 2005 table

Question

I have a small (200 rows / 400kb) table with 4 columns - nvarchar(MAX), nvarchar(50), and two ints. I'm having a problem with one particular row in which I can select and update the int fields, but when I attempt to select or update the nvarchar fields, the query runs indefinitely (at least 45 minutes before I cancel). I'm also unable to delete this row, or even truncate the table (again, the query runs indefinitely).

Any ideas? One of the int fields has a primary key, but there are no foreign keys.

Answer 1

run dbcc checkdb to see if your database has any problems or dbcc checktable

Answer 2

Are you sure that row isn't locked? Open a new connection, run your select query, note the SPID from either the top of the window 2000 / 2005 and the bottom 2008 management studio. In another window run sp_who2. Find the spid from the query running the record.

If you don't care about uncommitted data, or just want to test the row, do:

select * from table with (nolock) where key = 'mykey'

Answer 3

Looks like you have an uncommitted transaction locking things down.

You can free them up through the Activity Monitor. It is located in the Management folder of the database you are looking at.

Expand that, right click on Activity Monitor, and select View Processes. You can right click on processes and kill them there.

This isn't always the best solution, especially with a production database. You generally want to figure out why the transaction is not committed, and either manually roll it back or commit it.

Answer 4

if DBCC CHECKDB doenst't help like Thirster42 said

i would just copy data except problem row into newTable and drop the oldTable...

also i would check size of your TempDB.

Check Profiler, maybe there is trigger being fired?

See Execution plan for anything unusual..