This also brings up that simple question: "Are these databases being used - and who are using them?"
To solve this we have made this simple script that tracks the activity of the server, using the same basic views as the activity monitor does.
-- =============================================
-- Author: Rasmus Glibstrup, SQLGuyDK
-- Company: it-Craft
-- Create date: 03-Jun-2013
--
-- PART 01 - Create table
-- Description:
/*
SQL Access Monitor script
Script that captures activity on the SQL server
and saves it to a table.
The Script is based on the Activity Monitor
queries is SSMS
*/
-- =============================================
Create Database [SQLGuyDK_SQLAccessMonitor]
GO
USE [SQLGuyDK_SQLAccessMonitor]
GO
IF EXISTS(Select object_id from sys.tables where name ='SQLGuyDK_SQLAccessMonitor')
BEGIN
DROP TABLE [dbo].[SQLGuyDK_SQLAccessMonitor]
END
GO
CREATE TABLE [dbo].[SQLGuyDK_SQLAccessMonitor](
[SQLSERVER] [varchar](128) NOT NULL,
[Login] [varchar](128) NOT NULL,
[Database] [varchar](128) NOT NULL,
[Application] [varchar](255) NULL,
[LoginTime] [datetime] NULL,
[LastRequest] [datetime] NULL,
[HostName] [varchar](128) NOT NULL,
[NetAddress] [varchar](48) NULL
) ON [PRIMARY]
GO
-- =============================================
-- PART 02 - Script to capture activity
-- =============================================
Use SQLGuyDK_SQLAccessMonitor
GO
Create procedure dbo.[usp_SQLGuyDK_SQLAccessMonitor]
@RefreshIntervalInSeconds int = 10
as
SET NOCOUNT ON
Declare @DelayTime varchar(10)
SET @Delaytime = SUBSTRING(CONVERT(varchar, DATEADD(ms, @RefreshIntervalInSeconds * 1000, 0), 114),1,8)
WHILE 1=1
BEGIN
WAITFOR DELAY @Delaytime
MERGE [dbo].[SQLGuyDK_SQLAccessMonitor] as TARGET
USING (
SELECT
[SQLServer] = @@Servername
,[Login] = s.login_name
,[Database] = ISNULL(db_name(sp.dbid), N'')
,[Last Request Start Time] = MAX(s.last_request_start_time)
,[Host Name] = ISNULL(s.host_name, N'')
,[Net Address] = ISNULL(c.client_net_address, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.sysprocesses sp ON (s.session_id = sp.spid)
--Filter out only user processes
Where s.is_user_process = 1
-- do not include the query we are running
AND s.session_Id NOT IN (@@SPID)
Group by s.login_name,sp.dbid,s.host_name,c.client_net_address
) AS SOURCE ([SQLServer],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address])
ON SOURCE.[Login] = TARGET.[Login]
AND SOURCE.[SQLServer] = TARGET.SQLSERVER
AND SOURCE.[Database] = TARGET.[Database]
AND SOURCE.[Host Name] = TARGET.[HostName]
WHEN MATCHED THEN
UPDATE SET TARGET.[LastRequest]=SOURCE.[Last Request Start Time]
WHEN NOT MATCHED THEN
INSERT ([SQLSERVER],[Login],[Database],[LastRequest],[HostName],[NetAddress])
VALUES ([SQLSERVER],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address])
;
END
SET NOCOUNT OFF
GO
-- =============================================
-- PART 03 - Job to run script
-- =============================================
-- Create a job that is scheduled to run on sql server agent startup, and make it run this one command
exec SQLGuyDK_SQLAccessMonitor.dbo.[usp_SQLGuyDK_SQLAccessMonitor]
-- =============================================
-- PART 04 - Which databases are not being used?
-- =============================================
Select name
From Sys.databases
where name not in (
Select [Database]
from [dbo].[SQLGuyDK_SQLAccessMonitor])
And database_id > 4
1 comment:
IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.
JavaScript Training in Chennai
JavaScript Training in Chennai
Post a Comment