July 11, 2013 at 8:29 am
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
July 11, 2013 at 8:52 am
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.
July 11, 2013 at 9:04 am
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
July 11, 2013 at 9:13 am
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.
July 11, 2013 at 9:19 am
would you recommend any specific events that i shouild be collecting?
July 11, 2013 at 9:26 am
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.
July 11, 2013 at 11:23 am
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 🙂
July 15, 2013 at 7:28 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply