|
Home > Archive > MS SQL Server ODBC > October 2006 > SQL Blocking
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| I have data in SQL 2000 tables with Access XP front end linking to the tables
via a DSN-less connection.
Once in awhile we get sql blocking and I don't know why or how to solve it.
Today, I get a call from someone in the road dept that said that when they
go to update the driver data, it would hang for awhile and then time out with
the error:
ODBC - update on a linked table "tblDriver" failed.
[Microsoft][ODBC SQL Server driver] Timeout expired (#0)
I tried it myself and get the same error. There is about only 2-3 people
who are accessing that tblDriver at a time. I have the Access form display
only one record at a time too.
In the SQL enterprise manager, in the process info section, I noticed that
under Blocking, there is a 1 by my login for one of my processes (the update)
and under Blocking By, one of my ProcessID is displayed for another of my
ProcessID.
It's like one process of mine locked up another process of mine and everyone
else.
This doesn't happen often so I don't know how to fix it. Plus, the people
in the dept have worked on the table at one time often.
| |
| Sue Hoegemeier 2006-10-25, 6:01 am |
| Enterprise Manager is not really a good tool to use to
monitor these types of things - unless you want to refresh
it every couple of seconds, navigate back down to the
processes, back and forth, etc. Try using sp_who2 and
querying the sysprocesses table in the master database. To
monitor for blocking and log the results of blocking
activity, you can use the script in the following Microsoft
article:
How to monitor blocking in SQL Server 2005 and in SQL Server
2000
http://support.microsoft.com/?id=271509
-Sue
On Thu, 19 Oct 2006 13:21:02 -0700, ngan
<ngan@discussions.microsoft.com> wrote:
>I have data in SQL 2000 tables with Access XP front end linking to the tables
>via a DSN-less connection.
>
>Once in awhile we get sql blocking and I don't know why or how to solve it.
>
>Today, I get a call from someone in the road dept that said that when they
>go to update the driver data, it would hang for awhile and then time out with
>the error:
>
>ODBC - update on a linked table "tblDriver" failed.
>[Microsoft][ODBC SQL Server driver] Timeout expired (#0)
>
>I tried it myself and get the same error. There is about only 2-3 people
>who are accessing that tblDriver at a time. I have the Access form display
>only one record at a time too.
>
>In the SQL enterprise manager, in the process info section, I noticed that
>under Blocking, there is a 1 by my login for one of my processes (the update)
>and under Blocking By, one of my ProcessID is displayed for another of my
>ProcessID.
>
>It's like one process of mine locked up another process of mine and everyone
>else.
>
>This doesn't happen often so I don't know how to fix it. Plus, the people
>in the dept have worked on the table at one time often.
|
|
|
|
|