September 23, 2007 at 2:27 am
Comments posted to this topic are about the item Script to output dbcc inputbuffer adding spid info
DEX
😀
The more you help the business, the more they will help you...well sometimes anyway.
February 15, 2014 at 6:35 am
Fixed for typos and expanded data sizes in newer versions of sql server:
use master
go
alter procedure sp_inputbuffer
as
----------------------------------------------------------
----
-- THIS PROCEDURE WAS CREATED TO GET THE LATEST INPUT --
-- FROM THE VARIOUS PROCESSES RUNNING ON THE SERVER.--
-- IT COMBINES DBCC INPUT BUFFER WITH
-- CREATED BY SCOTT DEXTER--
-- DATABASE CONSULTANT--
-- CREATED ON 2006-09-29--
-- ALL RIGHTS RESERVED BY SCOTT M. DEXTER ALL USERS --
-- MUST SIGHT ME IN THE CREDITS FOR THIS PROCEDURE. --
----------------------------------------------------------
declare @currentuser sysname
declare @currentprocedure sysname
declare @emailsubject varchar(100)
declare @emailmessage varchar(max)
declare @time varchar(20)
declare @recipients varchar(100)
select @time = convert(varchar(20), getdate(), 120)
select @currentuser = rtrim(suser_sname())
select @currentprocedure = object_name(@@procid)
set @recipients = 'x@x.com'
------------------------------------------------------------------
-- CHECK THE PERMISSIONS OF THE PERSON EXECUTING THIS PROC.--
-- ONLY AUTHORIZED PERSONNEL WILL BE ALLOWED TO EXECUTE THIS--
-- PROCEDURE.--
------------------------------------------------------------------
if (not is_member('db_securityadmin') = 1) and
(not is_member('db_owner') = 1)
begin
set @emailsubject = 'Security Violation.'
set @emailmessage = 'The following user: ""' + @currentuser + '""' + char(10) + char(13) +
'does not have permission to run the following procedure: ""' + @currentprocedure + '""' + char(10) + char(13) +
'at the following date and time.' + @time
goto hellodolly
end
-- CREATES TEMP TABLE TO INSERT DATA INTO. THE
-- TABLE CAN BE ADDED TO FOR ADDITIONAL INFORMATION.
create table #inputbuffer
(
eventType varchar(255) ,
parameters int ,
procedureText varchar(max),
spid varchar(6)
)
declare @spid varchar(6)
declare @sql varchar(50)
-- CREATE CURSOR TO GET INFORMATION FROM DBCC INPUTBUFFER AND
-- ADDITIONAL COLUMNS INTO THE TEMP TABLE FOR OUTPUT TO SYSADMINS
-- AND THOSE SO FORTUNATE TO BE GRANTED THE AUTHORITY OF DBO.
declare sprocket cursor fast_forward for
select spid from master.dbo.sysprocesses
where program_name is null or program_name <> ''
open sprocket
fetch next from sprocket into
@spid
while @@fetch_status = 0
begin
set @sql = 'dbcc inputbuffer(' + @spid + ')'
insert into #inputbuffer(eventType, parameters, procedureText)
exec (@sql)
update #inputbuffer
set spid = @spid
where spid is null
-- ADDITIONAL UPDATES CAN BE PLACED HERE IF YOU SO DESIRE MORE INFORMATION.
-- PERSONALLY I THINK THAT THIS GIVES ME A GOOD PLACE TO START TO FIND ISSUES
-- THAT ARE GOING ON WHICH I CAN USE TO TROUBLESHOOT PERFORMANCE PROBLEMS OR
-- OTHER.
fetch next from sprocket into
@spid
end
close sprocket
deallocate sprocket
-- CHECK TO SEE IF THE CURSOR IS STILL OPEN.
if @@cursor_rows <> 0
begin
close sprocket
deallocate sprocket
end
-- OUTPUT THE INFORAMTION TO THE SCREEN FOR ANALYSIS.
select spid, eventType, parameters, procedureText
from #inputbuffer
-- IF THE TABLE STILL EXISTS (WHICH IT SHOULD) THEN DROP IT.
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return(0)
hellodolly:
begin
exec master.dbo.xp_sendmail @recipients = @recipients,
@subject = @emailsubject,
@message = @emailmessage
RETURN -1
end
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply