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

how i can identify local windows service that coresponds to sql spid? Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 8:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 201, Visits: 403
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



Post #1472610
Posted Thursday, July 11, 2013 8:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 1,421, Visits: 3,221
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.
Post #1472625
Posted Thursday, July 11, 2013 9:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 201, Visits: 403
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



Post #1472633
Posted Thursday, July 11, 2013 9:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 1,421, Visits: 3,221
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.
Post #1472641
Posted Thursday, July 11, 2013 9:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 201, Visits: 403
would you recommend any specific events that i shouild be collecting?


Post #1472644
Posted Thursday, July 11, 2013 9:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 1,421, Visits: 3,221
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.
Post #1472649
Posted Thursday, July 11, 2013 11:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 201, Visits: 403
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 :)



Post #1472721
Posted Monday, July 15, 2013 7:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 201, Visits: 403
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....

[url=http://www.sqlservercentral.com/Forums/Topic1264782-146-2.aspx][/url]





Post #1473645
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse