· 6 years ago · Aug 23, 2019, 01:42 PM
1-- =============================================
2-- Author: Rasmus Glibstrup, dba
3-- Company: it-Craft
4-- Create date: 03-Jun-2013
5--
6-- Modified by Søren Døssing July 2019
7-- twitter @edbsnedker
8--
9-- PART 01 - Create table
10
11-- Description:
12/*
13SQL Access Monitor script
14Script that captures activity on the SQL server
15and saves it to a table.
16
17The Script is based on the Activity Monitor
18queries is SSMS
19*/
20-- =============================================
21
22USE [dbalog]
23GO
24
25IF EXISTS(Select object_id from sys.tables where name ='dba_SQLAccessMonitor')
26BEGIN
27 DROP TABLE [dbo].[dba_SQLAccessMonitor]
28END
29GO
30
31CREATE TABLE [dbo].[SQLAccessMonitor](
32 [SQLSERVER] [varchar](128) NOT NULL,
33 [Login] [varchar](128) NOT NULL,
34 [Database] [varchar](128) NOT NULL,
35 [Application] [varchar](255) NULL,
36 [LoginTime] [datetime] NULL,
37 [LastRequest] [datetime] NULL,
38 [HostName] [varchar](128) NOT NULL,
39 [NetAddress] [varchar](48) NULL,
40 [ProgramName] [nvarchar](300) NULL,
41 [AuthScheme] [nvarchar](50) NULL
42) ON [PRIMARY]
43GO
44
45
46
47
48-- =============================================
49-- PART 02 - Script to capture activity
50-- =============================================
51Use [dbalog]
52
53GO
54Create procedure dbo.[usp_dba_SQLAccessMonitor]
55@RefreshIntervalInSeconds int = 10
56as
57SET NOCOUNT ON
58
59Declare @DelayTime varchar(10)
60
61SET @Delaytime = SUBSTRING(CONVERT(varchar, DATEADD(ms, @RefreshIntervalInSeconds * 1000, 0), 114),1,8)
62
63WHILE 1=1
64BEGIN
65 WAITFOR DELAY @Delaytime
66
67 MERGE [dbo].[SQLAccessMonitor] as TARGET
68 USING (
69 SELECT
70 [SQLServer] = @@Servername
71 ,[Login] = s.login_name
72 ,[Database] = ISNULL(db_name(sp.dbid), N'')
73 ,[Last Request Start Time] = MAX(s.last_request_start_time)
74 ,[Host Name] = ISNULL(s.host_name, N'')
75 ,[Net Address] = ISNULL(c.client_net_address, N'')
76 ,[Program Name] = ISNULL(s.program_name, N'')
77 ,[Auth Scheme] = ISNULL(c.auth_scheme, N'')
78 FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
79 LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
80 LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
81 LEFT OUTER JOIN
82 (
83 SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
84 FROM sys.dm_os_waiting_tasks
85 ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
86 LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)
87 LEFT OUTER JOIN sys.sysprocesses sp ON (s.session_id = sp.spid)
88 --Filter out only user processes
89 Where s.is_user_process = 1
90 -- do not include the query we are running
91 AND s.session_Id NOT IN (@@SPID)
92
93 Group by s.login_name,sp.dbid,s.host_name,c.client_net_address,s.program_name,c.auth_scheme
94
95 ) AS SOURCE ([SQLServer],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address],[Program Name],[Auth Scheme])
96 ON SOURCE.[Login] = TARGET.[Login]
97 AND SOURCE.[SQLServer] = TARGET.SQLSERVER
98 AND SOURCE.[Database] = TARGET.[Database]
99 AND SOURCE.[Host Name] = TARGET.[HostName]
100 AND SOURCE.[Net Address] = TARGET.[NetAddress]
101 AND SOURCE.[Program Name] = TARGET.[ProgramName]
102 AND SOURCE.[Auth Scheme] = TARGET.[AuthScheme]
103
104 WHEN MATCHED THEN
105 UPDATE SET TARGET.[LastRequest]=SOURCE.[Last Request Start Time]
106
107 WHEN NOT MATCHED THEN
108 INSERT ([SQLSERVER],[Login],[Database],[LastRequest],[HostName],[NetAddress],[ProgramName],[AuthScheme])
109 VALUES ([SQLSERVER],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address],[Program Name],[Auth Scheme])
110 ;
111
112END
113
114SET NOCOUNT OFF
115GO
116
117-- =============================================
118-- PART 03 - Job to run script
119-- =============================================
120
121-- Create a job that is scheduled to run on sql server agent startup, and make it run this one command
122
123USE [msdb]
124GO
125
126/****** Object: Job [_dba_SQLAccessMonitor] Script Date: 8/14/2019 3:33:54 PM ******/
127BEGIN TRANSACTION
128DECLARE @ReturnCode INT
129SELECT @ReturnCode = 0
130/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 8/14/2019 3:33:54 PM ******/
131IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
132BEGIN
133EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
134IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
135
136END
137
138DECLARE @jobId BINARY(16)
139EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_dba_SQLAccessMonitor',
140 @enabled=1,
141 @notify_level_eventlog=0,
142 @notify_level_email=0,
143 @notify_level_netsend=0,
144 @notify_level_page=0,
145 @delete_level=0,
146 @description=N'No description available.',
147 @category_name=N'[Uncategorized (Local)]',
148 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
149IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
150/****** Object: Step [run SQLAccessMonitor] Script Date: 8/14/2019 3:33:55 PM ******/
151EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run SQLAccessMonitor',
152 @step_id=1,
153 @cmdexec_success_code=0,
154 @on_success_action=1,
155 @on_success_step_id=0,
156 @on_fail_action=2,
157 @on_fail_step_id=0,
158 @retry_attempts=0,
159 @retry_interval=0,
160 @os_run_priority=0, @subsystem=N'TSQL',
161 @command=N'exec [dbalog].[dbo].[usp_dba_SQLAccessMonitor]',
162 @database_name=N'dbalog',
163 @flags=0
164IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
165EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
166IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
167EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Start when agent starts',
168 @enabled=1,
169 @freq_type=64,
170 @freq_interval=0,
171 @freq_subday_type=0,
172 @freq_subday_interval=0,
173 @freq_relative_interval=0,
174 @freq_recurrence_factor=0,
175 @active_start_date=20190814,
176 @active_end_date=99991231,
177 @active_start_time=0,
178 @active_end_time=235959,
179 @schedule_uid=N'94bc139d-f8bd-4622-a3ee-a945deb96703'
180IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
181EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
182IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
183COMMIT TRANSACTION
184GOTO EndSave
185QuitWithRollback:
186 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
187EndSave:
188GO
189
190
191
192
193-- =============================================
194-- PART 04 - Which databases are not being used?
195-- =============================================
196
197/**
198
199Select name
200From Sys.databases
201where name not in (
202Select [Database]
203from [dbo].[dbalog])
204And database_id > 4
205
206**/