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