Script to output dbcc inputbuffer adding spid info

  • ezlikesundaymorning

    SSC Veteran

    Points: 236

    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.

  • nospams

    SSC Veteran

    Points: 298

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply