SQL Server 监控linked server
1.创建extended eventCREATE EVENT SESSION [dbadmin_audit_linkserver] ON SERVER ADD EVENT sqlserver.oledb_provider_information( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)) ADD TARGET package0.event_file(SET filenameNdbadmin_audit_linkserver,max_file_size(50),max_rollover_files(10)) WITH (MAX_MEMORY4096 KB,EVENT_RETENTION_MODEALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY30 SECONDS,MAX_EVENT_SIZE0 KB,MEMORY_PARTITION_MODENONE,TRACK_CAUSALITYOFF,STARTUP_STATEON) GO ALTER EVENT SESSION [dbadmin_audit_linkserver] ON SERVER STATE START;2.每小时将数据导入表创建表CREATE TABLE [dbo].[dbadmin_audit_linkserver]( [LogDate] [datetime] NULL, [linked_server_name] [nvarchar](128) NULL, [provider_name] [nvarchar](128) NULL, [username] [nvarchar](128) NULL, [client_app_name] [nvarchar](255) NULL, [client_hostname] [nvarchar](255) NULL, [database_name] [nvarchar](128) NULL, [sql_text] [nvarchar](200) NULL, [file_name] [nvarchar](260) NOT NULL, [file_offset] [bigint] NOT NULL ) go CREATE NONCLUSTERED INDEX [idx_LogDate] ON [dbo].[dbadmin_audit_linkserver] ( [LogDate] ASC ) go --每天汇总一次 CREATE TABLE [dbo].[dbadmin_audit_linkserver_summary]( [linked_server_name] [nvarchar](128) NULL, [provider_name] [nvarchar](128) NULL, [username] [nvarchar](128) NULL, [client_app_name] [nvarchar](255) NULL, [client_hostname] [nvarchar](255) NULL, [database_name] [nvarchar](128) NULL, [sql_text] [nvarchar](200) NULL, [first_time] [datetime] NULL, [last_time] [datetime] NULL, [count_num] [int] NULL ) go数据从文件导入表declare file_name nvarchar(260) declare file_offset bigint declare LogDate datetime select LogDateisnull(max(LogDate),0),file_namemax(file_name),file_offsetmax(file_offset) from DBWatch.dbo.dbadmin_audit_linkserver where LogDate(select max(LogDate) from DBWatch.dbo.dbadmin_audit_linkserver); SELECT file_name,file_offset,CAST(event_data AS XML) AS event_data INTO #eventdata FROM sys.fn_xe_file_target_read_file(dbadmin_audit_linkserver*.xel, NULL, file_name, file_offset); insert into DBWatch.dbo.dbadmin_audit_linkserver(LogDate,linked_server_name,provider_name,username,client_app_name,client_hostname,database_name,sql_text,file_name,file_offset) SELECT dateadd(hour,8,event_data.value((event/timestamp)[1], DATETIME)) AS LogDate, event_data.value((event/data[namelinked_server_name]/value)[1], NVARCHAR(128)) AS linked_server_name, event_data.value((event/data[nameprovider_name]/value)[1], NVARCHAR(128)) AS provider_name, event_data.value((event/action[nameusername]/value)[1], NVARCHAR(128)) AS username, event_data.value((event/action[nameclient_app_name]/value)[1], NVARCHAR(255)) AS client_app_name, event_data.value((event/action[nameclient_hostname]/value)[1], NVARCHAR(255)) AS client_hostname, event_data.value((event/action[namedatabase_name]/value)[1], NVARCHAR(128)) AS database_name, LEFT(event_data.value((event/action[namesql_text]/value)[1], NVARCHAR(MAX)), 200) AS sql_text, file_name,file_offset from #eventdata清理数据insert into DBWatch.dbo.dbadmin_audit_linkserver_summary(linked_server_name,provider_name,username,client_app_name,client_hostname,database_name,sql_text,first_time,last_time,count_num) SELECT linked_server_name,provider_name,username,client_app_name,client_hostname,database_name,sql_text,min(LogDate) first_time,max(LogDate) last_time,count(1) count_num FROM [DBWatch].[dbo].[dbadmin_audit_linkserver] where LogDateCAST(GETDATE() AS DATE) group by linked_server_name,provider_name,username,client_app_name,client_hostname,database_name,sql_text; delete from [DBWatch].[dbo].[dbadmin_audit_linkserver] where LogDateCAST(GETDATE() AS DATE);
)
