Anyone have a script to capture a block?
-
For us poor folks that cannot afford good SQL monitoring tools...looking for a script or configure an alert when a block is greater than 5 minutes.
-
Start from http://support.microsoft.com/?kbid=271509
MJ
-
You can query the sys.dm_exec_requests for requests with a blocked_by that's not 0 and a wait_time exceeding your threshold.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks to both but I would like to automate this..I guess I am dreaming about turkey dinner
I cannot see putting this in as a job then I would have to schedule the job to run frequently.
I would like to establish the warning via the SQL Job Alert
Is that possible?
-
First create a alert of type(SQL Server Event Alert) and then chose severity of 016(Misc. User Error) for all databases. Then specify message text as "Blocking Threshold" . Then finally to get information about the blocking events encapsulate the information provided by us in stored procedure and call that stored procedure under Response-->Execute job(create a job calling the stored procedure created above).
Configure appropriate operators whom you want to notify.
MJ
-
Use following query:
select r.session_id, r.blocking_session_id, r.database_id, r.command, s.last_request_start_time, s.login_name, r.last_wait_type, r.status
from sys.dm_exec_requests r
join sys.dm_exec_sessions s on r.session_id = s.session_id
where (r.blocking_session_id > 0 and r.blocking_session_id <> r.session_id)
or r.session_id in (select session_id from sys.dm_exec_requests where blocking_session_id > 0 and blocking_session_id <> session_id)
or
SELECT spid, status, loginame=substring(loginame, 1, 12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
open_tran,dbname=substring(db_name(dbid),1,10),cmd,
waittype, waittime, last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked=0
-
Or you can put this in an agent job if you're on 2000. /It's a bit rough and ready but it gets you most of the way there
CREATE PROCEDURE [dbo].[FindOpenTransactions] AS
DECLARE @emailAddress VARCHAR(128)
SET @emailAddress = -- Put your distribution list here
--Create the required Tables IF they do not exist
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'u' and name = 'Open_transactions')
BEGIN
CREATE TABLE Open_transactions
(
spid int NULL,
login VARCHAR(32) NULL,
db VARCHAR(128) NULL,
hostname VARCHAR(64) NULL
)
END
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'u' and name = 'Open_transactions_history')
BEGIN
CREATE TABLE dbo.Open_transactions_history
(
Found_Date datetime NULL,
spid int NULL,
login VARCHAR(32) NULL,
db VARCHAR(128) NULL,
hostname VARCHAR(64) NULL,
program_name VARCHAR(128) NULL,
sqlCommandVARCHAR(2000) NULL
)
END
CREATE TABLE #InputBuffer
(
eventtype nVARCHAR(30) NULL,
params int NULL,
eventinfo nVARCHAR(255) NULL
)
/*Get list of processes with open transactions on the server*/
DECLARE curOpenTransProcs CURSOR
FOR SELECT
spid,
dbid,
hostname,
loginame,
program_name,
sql_handle
FROM master..sysprocesses
WHERE Open_tran > 0
DECLARE @spid int, @hostname VARCHAR(64), @login VARCHAR(32), @cmd VARCHAR(4000), @database VARCHAR(128), @program_name VARCHAR(128), @dbid int, @spidlist VARCHAR(2000), @wehavedata int
DECLARE @sql_handle-2 binary(20)
DECLARE @sqlCommand VARCHAR(2000)
/*Set initial values for flag / spid list variables*/
SET @spidlist = ''
SET @wehavedata = 0
OPEN curOpenTransProcs
FETCH NEXT
FROM curOpenTransProcs
INTO @spid, @dbid, @hostname, @login, @program_name, @sql_handle-2
WHILE @@fetch_status = 0
BEGIN
SET @wehavedata = 1
SELECT @database = name FROM master..sysdatabases WHERE dbid = @dbid
/*which processes with open transactions have been active since the last check
(rather than bombard recipients with anything that's open at all)*/
IF EXISTS
(
SELECT spid FROM open_transactions
WHERE
spid = @spid
and
login = @login
and
db = @database
and
hostname = @hostname
)
BEGIN
/*Add current spid to the list of 'ones of interest'*/
SELECT @spidlist = @spidlist + CONVERT(VARCHAR(32), @spid) + ','
SELECT @sqlCommand = ''
-- IF @sql_handle-2 <> 0x0
-- BEGIN
/*Was originally going to try and take SQL command info from
::fn_get_sql - but wasn't getting anything back. This, in theory
should allow us to spot the exact locking statement from a batch -
but, doesn't appear to return any value if, say, the issue's caused by
a transaction where the work's complete but awaiting a rollback/commit
command (say being run manually through QA). So - use scruffy
DBCC INPUTBUFFER approach instead
*/
DELETE FROM #Inputbuffer
SELECT @cmd = 'DBCC INPUTBUFFER (' + STR(@spid) + ') WITH NO_INFOMSGS'
PRint @cmd
INSERT #Inputbuffer EXEC(@cmd)
SELECT @sqlCommand = COALESCE(RTRIM(EventInfo), 'Unable to identify SQL command')
FROM #InputBuffer
/*Keep a list of problematic transactions for future reference */
INSERT INTO Open_Transactions_History
(
Found_Date,
spid,
login,
db,
hostname,
program_name,
sqlCommand
)
VALUES
(
getdate(),
@spid,
@database,
@hostname,
@program_name,
@sqlCommand
)
/*Send warning e-mail to appropriate recipients*/
SELECT @cmd = 'Host: ' + @hostname + CHAR(13)
SELECT @cmd = @cmd + 'Login: ' + @login + CHAR(13)
SELECT @cmd = @cmd + 'Database: ' + @database + CHAR(13)
SELECT @cmd = @cmd + 'SPID: ' + CONVERT(VARCHAR(6), @spid) + CHAR(13)
SELECT @cmd = @cmd + 'Program: ' + CONVERT(VARCHAR(20), @program_name) + CHAR(13)
SELECT @cmd = @cmd + 'SQL: ' + CHAR(13) + @sqlCommand + CHAR(13) + CHAR(13)
SELECT @cmd = @cmd + 'Procedure : FindOpenTransactions' + CHAR(13) + CHAR(13)
EXEC master..xp_sendmail @recipients = @emailAddress, @subject = 'Open Transaction Notification', @message = @cmd
END
ELSE
/*If it's a new proc with open transactions add it to the 'watch list'*/
BEGIN
INSERT INTO open_transactions
(
spid,
login,
db,
hostname
)
VALUES
(
@spid,
@database,
@hostname
)
SELECT @spidlist = @spidlist + CONVERT(VARCHAR(32),@spid) + ','
END
FETCH NEXT FROM curOpenTransProcs INTO @spid, @dbid, @hostname, @login, @program_name, @sql_handle-2
END
--Cleanup
DROP TABLE #InputBuffer
PRINT 'spidlist ' + @spidlist
IF @wehavedata = 1
BEGIN
/*Remove anything from the open transactions table where the spid no longer exists on the server*/
SELECT @cmd = 'DELETE FROM Open_transactions
WHERE spid not in ('
+ SUBSTRING( @spidlist, 1, LEN(@spidlist) -1) + ')' + CHAR(13)
print @cmd
EXEC (@cmd)
END
ELSE
DELETE FROM Open_transactions
CLOSE curOpenTransProcs
DEALLOCATE curOpenTransProcs
GO
+919924626601Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply