2

My database was running just fine this morning, up until it wasn't.

This query:

SELECT TOP (1) *
FROM tblTask;

comes back in no time at all, while this query:

SELECT *
FROM tblTask this_ with (updlock, rowlock);

which is generated by NHibernate in my app, is timing out in my software. In SSMS, I killed the query after letting it run for 9 minutes. The table has a total of 6 records.

The problem started coming up after the virtual machine that hosts the software running this query was rebooted this morning. Given the locking hint, I would have guessed that there was somehow a stray lock stuck on that table. I restarted the database, and also rebooted the server itself, but I still get the same behavior.

This table is only hit by a Windows service - nothing else should be locking the table and preventing access to it. The web site (which uses the rest of the database but not this table) appears to be running perfectly fine.

I'm not sure where to go from here. Any help?

4
  • Presumably it's a blocking issue. Find out what's blocking the process. Does more than one instance of the Windows service hit the table at the same time?
    – Jon Seigel
    Commented Dec 21, 2012 at 17:41
  • 1
    Grab a copy of sp_whoisactive and see what query(ies) you're fighting against?
    – billinkc
    Commented Dec 21, 2012 at 17:43
  • @JonSeigel Yes, I've been assuming a blocking issue as well; a little stumped cause nothing has changed, except for server patches. Commented Dec 22, 2012 at 1:46
  • @billinkc Thanks, I wasn't aware of that. I'll try it tomorrow. Commented Dec 22, 2012 at 1:47

1 Answer 1

1

Based upon my investigation with sp_whoisactive (thanks @billinkc), it was a blocking issue with the Windows service accessing the table. I found the issue in code and corrected it.

What is still unclear to me is why this was not an issue prior to the server reboot, why it suddenly started coming up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.