blocks hangup database when inserting in 1 specific table

Hi,

We use a database with about 40 related tables. Some tables contain as
much as 30.000 records. We use Access97 as an interface to the
database. Now recently we have the problem that when we want to insert
a row in one specific table (alwasy the same) the database makes
blocks.

Details:
- about 10% of the data was inserted using copying from Excel, before
this action there was no problem, though there is no evidence that
this causes the problem.
- inserting rows via the Query Analyzer works fine, via Access causes
trouble.
- the tempdb lofile has grown to 48Mb.

Has anyone ideas about what is going on and what I can do to solve the
problem?

TAV,
Jan WillemsJan Willems (jwillems@.xs4all.nl) writes:
> We use a database with about 40 related tables. Some tables contain as
> much as 30.000 records. We use Access97 as an interface to the
> database. Now recently we have the problem that when we want to insert
> a row in one specific table (alwasy the same) the database makes
> blocks.

"Makes blocks"? You mean that the INSERT operation is blocked, and you
have to cancel the operation to continue?

When the situation occurs, use sp_who from Query Analyzer, and see if
any process has a non-zero value in the Blk column. In such case, the
process listed in Blk, blocks the spid of that row.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Blocks and Locks in SQL

If you have an job interview question of how do handle SQL locks and blocks
whats your answer?"Lontae Jones" wrote:
> If you have an job interview question of how do handle SQL locks and block
s
> whats your answer?
I would talk about my experiences using Profiler, server-side traces, and
the really slick sp_blocker_pss80 lock / block analysis tool. I'd then ask
for them to put more context around the issues; for example: if the locking
condition was readily repeatable, I'd try to catch the lead blocker with
sp_who2, and run the DBCC INPUTBUFFER on the blocked/blocking SPID's to try
to get a handle on the situation causing the blocking. If the situation was
occurring in a distributed app and the sp_blocker_pss80 stored procedure was
reporting nested transactions, I'd start looking into how the app made DB
calls (i.e. - if they are asynchronous and nesting transactions, they may be
causing client app blocking issues that could be resolved by going a serial
route instead).
When I'm asked very broad questions I usually take the opportunity to flip
the situation on them and grill for specifics. The devil is always in the
details.|||Talk about transaction isolation level and NOLOCK Hints as well. I'm sure
that is what they're fishing for.
Greg Jackson
PDX, Oregon|||Look up Understanding and Avoiding Blocking in Books OnLine.
Here are a few suggestions from BOL to avoid blocking:
-Do not use or design an application that allows users to fill in edit boxes
that generate a long-running query. For example, do not use or design an
application that prompts the user for inputs but rather allows certain
fields to be left blank or a wildcard to be entered. This may cause the
application to submit a query with an excessive running time, thereby
causing a blocking problem.
-Do not use or design an application that allows user input within a
transaction.
-Allow for query cancellation.
-Use a query or lock time out to prevent a runaway query and avoid
distributed deadlocks.
-Immediately fetch all result rows to completion.
-Keep transactions as short as possible and in one batch.
-Explicitly control connection management.
-Stress test the application at the full projected concurrent user load.
Other suggestions:
-A query time-out for each query.
-Lock time-out for each query.
-Use a low isolation level.
-Use bound connections.
-Avoid using cursors
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:0E46E9EA-4975-4FC5-BAE3-6C1AE7D87D9C@.microsoft.com...
> If you have an job interview question of how do handle SQL locks and
> blocks
> whats your answer?

blocks & deadlocks

In the production server we are noticing blocks & dead-locks. I have used trace flag 1204 to capture info for deadlock which hapens very rarely.
I am seeing blocks every now & then. Is there any tools/system tables to query to get more info abt blocking & blocked spids. I used info from syslockinfo & sysprocess. dbcc inputbuffer gives info like 'sp_cursorunprepare;1' or 'sp_cursorfetch;1' and puts exclusive locks on bunch of tables. However no activity is seen for that spid.

Any help will be appreciated. I am using SQL Server 2000 with SP2

RachaelTry with sp_who2 and adding (NOLOCK) in your selects.
Csar.

Blocks

Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.Check out:
http://support.microsoft.com/default.aspx/kb/906344
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.|||Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>|||You'll have to tell us what you "configured".
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:eidPzzAyGHA.3844@.TK2MSFTNGP06.phx.gbl...
Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>

Blocks

Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.Check out:
http://support.microsoft.com/default.aspx/kb/906344
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
Hello,Why an sp autoblock '
If exec my sp and view the connections with sp_who i watch te id of my
connection in the column blk of the result.
Why succedes this '
Thanks in advance.|||Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>|||You'll have to tell us what you "configured".
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
news:eidPzzAyGHA.3844@.TK2MSFTNGP06.phx.gbl...
Thanks !!!
But,what i configured in the server because the time of blocked is over 20
seconds ?
Thanks in advance.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23mmGQy%23xGHA.2516@.TK2MSFTNGP06.phx.gbl...
> Check out:
> http://support.microsoft.com/default.aspx/kb/906344
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Luis Tarzia" <tarzialuis@.ciudad.com.ar> wrote in message
> news:euJLKt%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hello,Why an sp autoblock '
> If exec my sp and view the connections with sp_who i watch te id of my
> connection in the column blk of the result.
> Why succedes this '
> Thanks in advance.
>

Blocking

Have have process that blocks each other in SQL 7.0. How do I nuke these
processes?
KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:

> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?
|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Blocking

Have have process that blocks each other in SQL 7.0. How do I nuke these
processes?KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:

> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Blocking

Have have process that blocks each other in SQL 7.0. How do I nuke these
processes?KILL (spID)
spID is the process ID
http://sqlservercode.blogspot.com/
"Arne" wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke these
> processes?|||Arne wrote:
> Have have process that blocks each other in SQL 7.0. How do I nuke
> these processes?
You can kill one of the SPIDs using the KILL <spid> command. That will
force a disconnect and subsequent rollback of the currently running
transaction.
But if they are truly blocking each other, they would probably deadlock.
And that would be resolved immediately by SQL Server. Are you sure that
there is not only one transaction that is causing others to wait for
resources? If so, figure out which spid is at fault and first see if the
user can disconnect gracefully from the application (assuming it is a
real user and not some system process). If not, then kill it. I would
encourage you to first determine what SQL is running using fn_get_sql()
and/or DBCC INPUTBUFFER in order to better address this problem in the
future.
David Gugick
Quest Software
www.imceda.com
www.quest.com