Red Gate forums :: View topic - blocked process
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

blocked process

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
xdai



Joined: 31 Dec 2008
Posts: 69

PostPosted: Wed Feb 27, 2013 4:16 pm    Post subject: blocked process Reply with quote

Hi,

Frequently, we see the sql fragment on blocked process alert. The blocking process shows this, what does this mean?

thanks


Blocking process:

BusinessObjects Enterprise


Process ID:

132

create procedure sys.sp_stored_procedures
(
@sp_name nvarchar(390) = null, -- Wildcard pattern matching is supported.
@sp_owner nvarchar(384) = null, -- Wildcard pattern matching is supported.
@sp_qualifier sysname = null,
@fUsePattern bit = 1 -- To allow users to explicitly disable all pattern matching.
)
as
declare @full_sp_name nvarchar(775) -- 384 + 1 + 390
declare @sp_id int
declare @use_system bit

if @sp_qualifier is not null
begin
if db_name() <> @sp_qualifier
begin
if @sp_qualifier = ''
begin
-- in this case, we need to return an empty result set
-- because the user has requested a database with an empty name
select @sp_name = ''
select @sp_owner = ''
end
else
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end
end

if @sp_name = '%'
select @sp_name = null
if @sp_owner = '%'
select @sp_owner = null

if @sp_name is not null
begin
if (@sp_owner is null) and (charindex('%', @sp_name) = 0)
begin
if exists (
select *
from
sys.spt_all_procedures pro
where
pro.schema_id = schema_id() and
pro.name = @sp_name
)
begin
select @sp_owner = schema_name()
end
end
end

select @full_sp_name = isnull(quotename(@sp_owner), '') + '.' + isnull(quotename(@sp_name), '')
select @sp_id = object_id(@full_sp_name)

if (@fUsePattern = 1) -- Does the user want it?
begin
if ((isnull(charindex('%', @full_sp_name),0) = 0) and
(isnull(charindex('_', @full_sp_name),0) = 0) and
(@sp_id <> 0))
begin
select @fUsePattern = 0 -- not a single wild char, so go the fast way.
end
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Fri Mar 01, 2013 4:57 pm    Post subject: Reply with quote

Hello,

SQL Monitor will tell you that a process has a wait put on it for longer than a specified threshhold. When the blocking condition is over, SQL Monitor will mark the alert as ended.

Blocking could be because of resource problems on the server or an object that is locked.

SQL Monitor is telling you that the BusinessObjects Enterprise process is trying to create a stored procedure. It should also show the blocking process, which should hopefully give a clue what the issue could be.

If something is creating a schema object in a database, it could be that the query is using a transaction isolation level like SERIALIZABLE - I know from experience that SQL Compare does this to prevent schema corruption so possibly the BusinessObjects process is doing something similar.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group