SPID history

  • I am getting some rather odd behavior on a server (Agent getting turned off, agent XP's getting disabled / enabled) and am trying to track down the cause. Is there any way to see who used to have a SPID?

    I am hoping to track down whoever did this:

    2011-02-07 08:34:04.24 spid51 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    2011-02-07 08:34:04.26 spid51 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

    2011-02-07 08:34:04.30 spid51 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

    While I'm at it, anyone having issues with SP 4? That's the only oddball thing about this server that I can identify.

    Thanks.

  • First place to look is the windows application log, SQL log and SQL agent logs. See if you can fathom whether someone is sending a stop command.

  • SPIDs are reused, and I don't think you can track this down. Not sure if anything that you are looking for is in the default trace, but worth a look.

    The best thing you can do is enable some auditing, maybe tracking through SQL TRace of things happening in msdb or master and trying to track down what is happening.

  • I did find some information in the default trace, but it's not making much sense. Someone did sent a stop request. Who I don't know. The SPID 51 was SQL Agent itself.

    ApplicationName = 'SQLAgent - Initial Boot Probe'

    Still not making much sense to me.

  • well spid 51 is the first available spid to a non system process so i would guess its a job/application thats connecting as soon as SQL starts up.

    assuming the use of spid 51 is not constantly changing try running dbcc inputbuffer (51) and see what transactions its running to see if you can work out what it is

  • I am not in work today so cannot check but i am fairly certain those messages are standard on startup of single server. SQL agent does not come online until all databases are recovered, at which point agent XPs are enabled.

    Check all your error logs for comparison, I don't think you have an issue here.

    ---------------------------------------------------------------------

  • george sibbald (2/7/2011)


    I am not in work today so cannot check but i am fairly certain those messages are standard on startup of single server. SQL agent does not come online until all databases are recovered, at which point agent XPs are enabled.

    Check all your error logs for comparison, I don't think you have an issue here.

    yes they do appear on every start/stop of the agent service. the way i read the OP was that the service was being stopped when it wasnt meant to be and thats what they are trying to track down.

    If all you are worried about is the log entries then yes its normal

  • What I'm worried about is log entries which indicate that Agent XPs are disabled when they're not (during a production job run):

    2011-02-07 02:13:00.98 spid55 SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

    logging onto the DB server and seeing SQL Agent not running even though it should be and seeing a stop request in the SQL Server logs when nobody has any business stopping the agent.

    2011-02-07 08:33:59 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...

    2011-02-07 08:34:02 - ? [098] SQLServerAgent terminated (normally)

  • Now we are into a different question Pam, your original post just showed agent xps starting up.

    so this is not happening on startup and stop of SQL?

    Run a profiler trace capturing login id, ntusername and also info on who stopped services will be in the application log in eventvwr.

    ---------------------------------------------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

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