timeout expired error

Question

timeout expired error

Denise McMillan

SSCertifiable

Points: 6216
More actions
April 7, 2008 at 12:35 pm

#120916

In a test database I get timeout expired error when running a simple "select * from obs_result" query. The table only has 9 rows, other tables in the database respond with no problem.
This is a MSSQL 2005 developer ed. setup.
Error is:
SQL Execution error.
Executed SQL statement: select ...
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
Any ideas as to where I should look to fix this? Thanks very much for any help.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

Could be that you're being blocked by another process. In SSMS open up the Activity Monitor under Management. Look at the Blocked and Blocked By columns.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

I would suspect a blocking process.

Try "select * from obs_result (NOLOCK)"

If this works, you have another process with a lock on at least one row in the table. You can then use sp_who2 to figure out what is blocking you.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 3

I ran the select with nolock and it worked great. I ran without and see blocked by -2, results of sp_who2:

56 SUSPENDED MGH\dmcmillanMGH-9750-05490-2 eorlaSELECT 156004/07 :12:50Microsoft SQL Server Management Studio - Query56 0

What do I have to do to correct this? Thanks very much for the help.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 4

To kill a MSDTC transaction use the Unit of Work ID with the KILL command.

USE master

SELECT req_transactionUOW

FROM syslockinfo

WHERE req_transactionUOW <> CONVERT(UNIQUEIDENTIFIER,0x0)

KILL 'UOW value'

Reference:

BOL: KILL (Transact-SQL)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

By the looks of the application and user name from sp_who2, you are the problem.

You have management studio open with an open transaction locking the table you are trying to query.

Look through your open query windows and find the one for spid 56. Commit or roll back your open transaction.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 6

Michael Earl (4/7/2008)
By the looks of the application and user name from sp_who2, you are the problem.
You have management studio open with an open transaction locking the table you are trying to query.
Look through your open query windows and find the one for spid 56. Commit or roll back your open transaction.

Michael,

I could be reading that sp_who2 output wrong but isn't SPID 56 the one that's being blocked and -2 the blocker?

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 7

That's right, sp56 is my connection being blocked by -2.

I don't see a -2 in the list of sp_who2 though. How can I tell if I should kill it? Thanks so much!