Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SD1999
SD1999
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 414
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



sturner
sturner
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 Visits: 3254
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.
SD1999
SD1999
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 414
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



sturner
sturner
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 Visits: 3254
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.
SD1999
SD1999
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 414
would you recommend any specific events that i shouild be collecting?



sturner
sturner
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 Visits: 3254
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.
SD1999
SD1999
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 414
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 Smile please... i am all open to suggestions Smile



SD1999
SD1999
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 414
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]



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search