Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g. select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on end .

the end user querys run just fine when I test the. It seems to get into a blocking issue when qanother process tries to truncate and reload a table.

how can I troubleshoot this?

share|improve this question
    
If it is SELECT-ing from a table that has been TRUNCATE-d by another still open transaction then yes it will be blocked. Are you saying the blocking continues even after that other transaction is completed? –  Martin Smith Jan 4 '13 at 12:43
    
No its a select statemetn that normally runs (e.g. when i test it) in < 30 minutes, but it sometimes runs endlessly (and while it is running endlessly, it prevents a nightly etl process from truncating a table). I guess i should be looking into why it runs forever, and that would solve the overall issue. –  robarwebservices Jan 4 '13 at 12:52
3  
Ah I see. Well I guess either the SELECT is itself blocked by something else (look in sys.dm_os_waiting_tasks) or sometimes it gets a catastrophically bad plan that means it is actually doing much more work (e.g. parameter sniffing). You say "the end user querys run just fine when I test" so sounds like the second one. Compare and contrast the execution plans from both sessions. –  Martin Smith Jan 4 '13 at 12:57

1 Answer 1

The easiest way I look at it is, right click on the database in SSMS and under Reports => Standard Reports => All Blocking Transactions. There are other standard reports with blocking transactions as well but I found all one pretty useful.

The other thing you could do is setup a SQL Server Agent Alert for figuring our locks for your database.

Type: SQL server performance condition alert Object: SQL Server:Locks Counter: Anything you want Instance: Database Rises above: {x} number of occurrences you want to look for.

And have the alert set to email you when the condition is met (considering you have Database Mail setup already).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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