Header

Wednesday, February 19, 2014

Who is using my databases

Due to Microsofts change in licensing with SQL Server 2012, alot of our customers is currently doing consolidation projects or migrating databases from older servers so these can be closed.

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:

for ict 99 said...

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