Timeout Expired Error

[Ryan P. Kennedy]

Hello:

I have an application (using ADO 2.7 - written in VB) that access a SQL
Server 2000 Database.

Every once in a while, the program hangs when I am accessing the DB, and
sometimes I get a timeout expired error. The wierd thing is, when it hangs,
if you try the function again - it goes throught every time

For example, If I call a function that retrieves a password from a table in
the DB, it will hang (sometimes it hangs for a few seconds and then goes
throught, sometimes it will timeout and gives me the timeout expired error).
If I then call the same function immediately after, there will be no delay.

I am stumped on this, any help would be appreciated. I have tried shrinking
the DB and Backing it up to no avail.

Regards,
Ryan Kennedy

[Erland Sommarskog]

Ryan P. Kennedy (ry*****@verizon.net) writes:

I have an application (using ADO 2.7 - written in VB) that access a SQL
Server 2000 Database.

Every once in a while, the program hangs when I am accessing the DB, and
sometimes I get a timeout expired error. The wierd thing is, when it
hangs, if you try the function again - it goes throught every time

For example, If I call a function that retrieves a password from a table
in the DB, it will hang (sometimes it hangs for a few seconds and then
goes throught, sometimes it will timeout and gives me the timeout
expired error). If I then call the same function immediately after,
there will be no delay.

I am stumped on this, any help would be appreciated. I have tried
shrinking the DB and Backing it up to no avail.

The most likely cause would of course be blocking. To investiagte this,
next time, you get stuck, issue an sp_who from Query Analyzer, and keep
an eye on the Blk column. A non-zero value in this column indicates that
the spid on this row is blocked by the spid in the Blk column.

You can also use the undocumented sp_who2 to get the same information,
but some some more information.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp