how i can identify local windows service that coresponds to sql spid?

  • Good morning Everyone,

    Our environment: sql server 2008 r2 sp2 on windows 2008 r2 enterprise sp1, 2 node active/passive cluster; 200 (500mb databases)

    Approximately every 10-15 minutes i see multiple (per database) connections are being established…those would run for a minute and disconnect

    It’s not really causing any performance issues….but, 600-800 connections to server that comes and goes are driving me crazy.

    I cannot identify WHERE are they coming from….i do see that some local process initiating them…cannot figure out which one.

    All I see is

    sql User: NT AUTHORITY\SYSTEM

    host name : local physical node name

    Program: .Net SqlClient Data Provider

    Net Library: TCP

    Win Domain : NT AUTHORITY

    Win User: SYSTEM

    I see that these are just sql server related information collections and we do have 3rd party monitoring tools, which I currently disabled.

    Can these be just a sql server native status info collections?????

    And these are the query that it runs:

    Connection1:

    SELECT [dbfiles].[physical_name] AS DBFilesPhysicalName,[mediafamily].[physical_device_name] AS BackupFilesPhysicalName FROM msdb.dbo.backupmediafamily [mediafamily] JOIN (SELECT [backupsets].media_set_id, [backupsets].database_name FROM msdb.dbo.backupset [backupsets] JOIN (SELECT TOP 1 [backupsets].media_set_id, [backupsets].database_name, [backupsets].backup_finish_date FROM msdb.dbo.backupset [backupsets] JOIN sys.databases [databases] ON [backupsets].[database_name] = [databases].[name] and [databases].[is_in_standby] = 0 AND [databases].[source_database_id] IS NULL AND ([databases].name = 'master' OR DATEDIFF ( ss, [databases].[create_date] , [backupsets].[database_creation_date] ) = 0 ) WHERE [databases].[database_id] = DB_ID() AND [backupsets].type = 'D' ORDER BY [backupsets].backup_finish_date DESC) AS [latest_backupset] ON [backupsets].[database_name] = [latest_backupset].[database_name] AND [backupsets].[backup_finish_date] >= [latest_backupset].[backup_finish_date]) AS latest_backups ON [latest_ba

    Connection2:

    select * from sys.master_files where database_id = '20' and type in (0,1)

    Connection3:

    (@DatabaseName nvarchar(10))IF (Exists(SELECT * FROM(SELECT name as 'DatabaseName' FROM sys.databases WHERE name NOT IN ('master','model','tempdb','msdb' ) and source_database_id IS NULL) AS UserDatabase WHERE UserDatabase.DatabaseName = @DatabaseName))BEGIN SELECT distinct obj.name FROM sys.objects AS obj JOIN sys.stats stat ON stat.object_id = obj.object_id CROSS apply sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE obj.type = 'U' AND modification_counter > 1000 END

  • Sounds like it might be one of your SQL Agent jobs. Is your SQL Agent service running as a real local or domain user account or just local NT authority (system)?

    Look in the agents job history that should tell you when any agent jobs are running, how long they take to execute and from that you can see if they correspond to these queries you are seeing.

    The probability of survival is inversely proportional to the angle of arrival.

  • I did looked at the agent's jobs history...nothing there

    all it had is a number of 'my' maintenance jobs, status report daily jobs, etc...

    nothing that will have every 15 minutes schedule there....

    and sql agent is running under domain account

  • So the next step is to look at windows System/Application/Security logs to see if any entries correspond with these process executions. Also set up a trace and try and capture more information about these queries.

    The probability of survival is inversely proportional to the angle of arrival.

  • would you recommend any specific events that i shouild be collecting?

  • collect whatever you can, hopefully you can set up a filter to trace only activity from this login and against the database involved in the queries.

    The probability of survival is inversely proportional to the angle of arrival.

  • i did setup a few filters.....nope, nothing there

    i am capturing all the connections that i am interested in...

    however,

    not much info you can get trough sql profiler on internal\hidden\background processes

    assuming that this is Not internal sql collection, does this means that one of the 3rd party monitoring tool

    that is a member of local admin group (windows admin) using NT AUTHORITY\SYSTEM login to connect to sql?

    any other thoughts, ideas 🙂 please... i am all open to suggestions 🙂

  • Turns out System Center Operations Manager Agent was running HealthService.exe as Local System

    and firing up those 1600 connections every 15 minutes.

    Here is the reference to that forum that helped....

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

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