Cascading Blocking ?

We have a situation that occurs every so often with blocking of
various databases on one server (Win200 SQL7). It appears to happen at
random, so I'm assuming it originates from something a user does and
not a regularily run process.

We've examined the data available to us and used the very helpful
blocking code on http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
(Thanks Erland).

Were getting closer to finding the problem, but need some advice on
what to look for.

This is a bit of guesswork, but we suspect that we get into a
situation where blocking takes places, and this then cascades to other
processes which then block others in turn. The original culprit then
finishes, but the blocks continue as the newer processes are holding
something else up. A bit like dominoes. It seems to take a while to
free this up.

The problem we have is determining the start of this process. Once we
are made aware of blocking issues, we can find out who is doing what,
but almost always get a different answer/user and think we're getting
to it a little late.

Ideally, I want to log the blocking somewhere so I can examine the
files when this occurs and can therefore establish a pattern etc...

Any ideas or suggestions would be welcome.Take a look at
http://support.microsoft.com/defaul...kb;EN-US;251004.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0310170116.c4b64ed@.posting.google.co m...
> We have a situation that occurs every so often with blocking of
> various databases on one server (Win200 SQL7). It appears to happen at
> random, so I'm assuming it originates from something a user does and
> not a regularily run process.
> We've examined the data available to us and used the very helpful
> blocking code on
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
> (Thanks Erland).
> Were getting closer to finding the problem, but need some advice on
> what to look for.
> This is a bit of guesswork, but we suspect that we get into a
> situation where blocking takes places, and this then cascades to other
> processes which then block others in turn. The original culprit then
> finishes, but the blocks continue as the newer processes are holding
> something else up. A bit like dominoes. It seems to take a while to
> free this up.
> The problem we have is determining the start of this process. Once we
> are made aware of blocking issues, we can find out who is doing what,
> but almost always get a different answer/user and think we're getting
> to it a little late.
> Ideally, I want to log the blocking somewhere so I can examine the
> files when this occurs and can therefore establish a pattern etc...
> Any ideas or suggestions would be welcome.

Capturing the Error Description in a stored procedure

Hi,

I have a few stored procedure which will be executed one after another. If any error occurs, i need capture the Error number and ERROR DESCRIPTION and SAVE it into a table within stored procedure itself. Any idea how to do it?

I saw a similar problem from http://www.sqlservercentral.com/columnists/ajethva/capturingtheerrordescriptioninastoredprocedure.asp but i cannot download the sample code.

i want to CAPTURE the following msg :

e.g. Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__au_id__0519C6AF'.
The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

It would be great if you could send sample code.

thanks.

rama

If you use SQL server 2000, you can use the following batch. But you can’t suppers the error message.

Code Snippet

Create table ErrorLog

(

Source nvarchar(100),

CalledUser varchar(100),

ErrorNumber int,

ErrorDescription nvarchar(2000),

ErrorDatetime datetime

)

Go

Create proc MyProc

@.I as int

as

Declare @.Error as int

Select 1/@.I

Set @.Error = @.@.ERROR

If @.Error <> 0

Begin

Insert Into ErrorLog

select

object_name(@.@.PROCID),

suser_sname(),

@.Error,

description,

getdate()

from

Sysmessages

Where

error=@.Error

and msglangid = (select msglangid from syslanguages where name='us_english') -- You can change to your local language

End

Go

Exec MyProc 1

Go

Exec MyProc 0

Go

select * from ErrorLog