Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to output dbcc inputbuffer adding spid info Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 2:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 16, 2008 1:20 PM
Points: 102, Visits: 25
Comments posted to this topic are about the item Script to output dbcc inputbuffer adding spid info

DEX
:D
The more you help the business, the more they will help you...well sometimes anyway.
Post #401532
Posted Saturday, February 15, 2014 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 10, 2014 12:18 PM
Points: 8, Visits: 50
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


Post #1541842
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse