"Orphaned" connection
-
Hello Everyone,
Less than a week before Christmas... wow, the time is flying.
Is there someone that knows a tool or a way to detect and kill what is called orphaned connection on the server (orphaned connection are pften caused by appplication that end abnormally)?
We would avoid that.
Regards,
-
Hi Carl,
Are you refering to an orphaned connection caused by an extended stored procedure that terminated but left the connection open?
If so I do have a solution (other than stop/start) but i've never wanted to run it on a production system.
If you're interested give me a shout and i'll post the details
-
Hello Mike,
No not exactly. We don't you extended stored procedure.
I talk about orphaned connections caused by an abnormal end of an application that was connected to the server.
But you can post your script, it could help... we never know.
Regards,
Carl
-
I daily check the Current Activity and look to see if there are any processes initiatiated by users that are two days old or older based on Last Batch Date/Time. Usually I kill those processes but I sure wouldn't trust something that is automatic.
Steve
-
A daily human check let an open door for huge problems... If an orphaned connection keep a table lock for an hour and everyone get kept in a busy wait line...
Oracle have a functionallity for that it is called D.C.D. (Dead Connection Detection) and it work fine... Maybe Microsoft have something similar...
Regards,
Carl
-
Sorry it took me a day to get back to you, my scripts won't do you any good for what you are trying to do.
I think it would probably be more appropriate to look at implementing some for of blocking monitoring for the SQL Server, that way if a process gets blocked you get an email and there's a log entry that you can check.
I think we have a customised version of one written by Ken Henderson on our live server, i'll have a look and get back to you if I think it would be easy to implement on another server.
Mike
-
Ok thanks Mike.
-
Hi Carl, no doubt there are loads of different versions of this floating around but this is the one we use.
Create the 1st SP in the master database
-------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_find_root_blocker @help char(2)=NULL
/*
Object: sp_find_root_blocker
Description: Finds the root offender(s) in the chain(s) of blocked processes
Usage: sp_find_root_blocker
Returns: spid of the root blocking process (returns the last one if there are multiple)
Created by: Ken Henderson. Email: khen@khen.com
Version: 6.0
Example: sp_find_root_blocker
Created: 1992-11-03. Last changed: 1999-07-05.
*/
AS
set nocount on
IF (@help='/?') GOTO Help
IF EXISTS (SELECT * FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked)) BEGIN
DECLARE @spid int
SELECT @spid=p1.spid -- Get the _last_ prime offender
FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked)
WHERE p1.blocked=0
SELECT p1.spid,
p1.status,
loginame=LEFT(p1.loginame,20),
hostname=substring(p1.hostname,1,20),
blk=CONVERT(char(3),p1.blocked),
db=LEFT(db_name(p1.dbid),10),
p1.cmd,
p1.waittype
FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON (p1.spid=p2.blocked)
WHERE p1.blocked=0
if @spid is null begin set @spid = 0 end
RETURN(@spid) -- Return the last root blocker
END ELSE BEGIN
-- PRINT 'No processes are currently blocking others.'
RETURN(0)
END
RETURN 0
Help:
EXEC sp_usage @objectname='sp_find_root_blocker', @desc='Finds the root offender(s) in the chain(s) of blocked processes',
@parameters='', @returns='spid of the root blocking process (returns the last one if there are multiple)',
@author='Ken Henderson', @email='khen@khen.com',
@version='6', @revision='0',
@datecreated='19921103', @datelastchanged='19990705',
@example='sp_find_root_blocker'
RETURN -1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------------
-
then create the following 2 tables in a DB
CREATE TABLE [dbo].[BlockedEvent] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[BlockedByID] [int] NOT NULL ,
[EventType] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parameters] [int] NULL ,
[EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BlockDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_Blocked] ON [dbo].[BlockedEvent]([BlockedByID], [EventInfo]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE TABLE [dbo].[BlockingEvent] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[BlockRootID] [int] NOT NULL ,
[EventType] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parameters] [int] NULL ,
[EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BlockDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CIX_Blocking] ON [dbo].[BlockingEvent]([BlockRootID], [BlockDate], [EventInfo]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
-
then create these 2 SP's in the same DB as the table
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.spAuditBlockedEvents
@BlockingProcessID int,
@BlockingEventID int
AS
DECLARE @ProcessID int
DECLARE @UTCNow datetime
DECLARE @strSQL nvarchar(4000)
DECLARE @EventID int
SET @UTCNow = getUTCdate()
DECLARE BaseCurs CURSOR FORWARD_ONLY STATIC READ_ONLY FOR
SELECT p1.spid
FROM master..sysprocesses p1
WHERE p1.blocked=@BlockingProcessID
OPEN BaseCurs
FETCH BaseCurs INTO @ProcessID
CREATE TABLE #tempBlocked
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @strSQL = 'DBCC INPUTBUFFER (' + convert(varchar, @ProcessID) + ') WITH NO_INFOMSGS'
INSERT INTO #tempBlocked
exec sp_executesql @strSQL
--Write details of blocked events to Blocked table
INSERT dbo.BlockedEvent
Select @BlockingEventID AS BlockRootID, EventType AS EventType, Parameters AS Parameters, EventInfo AS EventInfo, @UTCNow AS BlockDate FROM #tempBlocked
drop table #tempBlocked
--select @EventID = @@IDENTITY
--exec spAuditBlockedEvents @BlockingProcessID = @ProcessID , @BlockingEventID = @EventID
FETCH BaseCurs INTO @ProcessID
END
CLOSE BaseCurs
DEALLOCATE BaseCurs
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.spAuditBlockingEvents @SMTP nvarchar(100)
AS
set nocount on
DECLARE @spid int
DECLARE @BlockRootID int
DECLARE @UTCNow datetime
DECLARE @Output nvarchar(4000)
DECLARE @strSQL nvarchar(4000)
SET @UTCNow = getUTCdate()
SET @BlockRootID = 0
SET @strSQL = 'exec sp_find_root_blocker'
CREATE TABLE #tempRoot
(
[spid] [int] ,
[status] [nchar] (30) ,
[loginname] [nchar] (128) ,
[hostname] [nchar] (128) ,
[blk] [int] ,
[db] [nchar] (128) ,
[cmd] [nchar] (128) ,
[waittype] [binary] (2)
)
INSERT INTO #tempRoot
exec sp_executesql @strSQL
select @spid = spid from #tempRoot
IF @spid > 0
BEGIN
--Write the details of the root block to the Root Blocker table
INSERT dbo.BlockRoot
Select spid AS spid, status AS status, loginname AS loginname, hostname AS hostname, blk as blk, db as db, cmd as cmd, waittype as waittype, @UTCNow AS BlockDate FROM #tempRoot
SELECT @BlockRootID = @@IDENTITY
CREATE TABLE #tempBlocks
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @strSQL = 'DBCC INPUTBUFFER (' + convert(varchar, @spid) + ') WITH NO_INFOMSGS'
INSERT INTO #tempBlocks
exec sp_executesql @strSQL
--Write details of blocking event to Blocks table
INSERT dbo.BlockingEvent
Select @BlockRootID AS BlockRootID, EventType AS EventType, Parameters AS Parameters, EventInfo AS EventInfo, @UTCNow AS BlockDate FROM #tempBlocks
drop table #tempBlocks
exec dbo.spAuditBlockedEvents @BlockingProcessID = @spid, @BlockingEventID = @BlockRootID
-------------------------------------------------
--check the blocked table, if its the same or null then dont bother sending the email, otherwise go ahead (added to ignore new stuff from SP4)
declare @sendemail int
select @sendemail = count (*) from
(select top 100 biv.blockrootid, biv.eventinfo as biv, bev.eventinfo as bev from blockedevent bev
inner join blockingevent biv on bev.blockedbyid = biv.blockrootid
where bev.eventinfo != biv.eventinfo
and biv.blockrootid = (select max(be.blockrootid) from blockingevent be)
order by biv.blockrootid desc)mm
if @sendemail >=1
begin
DECLARE @Message nvarchar(200)
SET @Message = 'Blocking has occurred in the database. Please investigate BlockRootID = ' + convert(varchar, @BlockRootID) + ' in the BlockingEvent table.'
EXEC master..sp_SQLSMTPMail
@vcTo = 'mike@here.com',
@vcFrom = 'Servername',
@vcCC = 'someone.else@here.com',
@vcsubject = 'Blocking Detected on SQL Server',
@vcBody = @message
end
END
drop table #tempRoot
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
You'll need to amend the last SP to use the STMP mail version of your choice, we use this one but I'm sure the others will be fine
--- mental note, should have put this in the scripts area
All that's left when these are all in place is to create a scheduled job to run the spAuditBlockingEvents procedure as often as you like.
I've just looked at the SPs for the first time in a while and they use cursors
, i'm going to have to go and do them properly now that I know they're there.
-
Hello Mike,
thank's, I'll have a look at that as soon as possible.
Should be an improvement in SQL Server itself.
Regards,
Carl
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply