How to Get the Usage Statistics of Your SQL Server Databases

When working in a very large SQL Server environment, it is very common that no one in the organization knows who uses a particular database. This scenario is very common if there are multiple legacy systems. Follow this article to identify how effectively your SQL Server databases are used.

Method 1:

In this method, we are going to read the output of sp_who2 and capture it in a table. The first step is to create the table using this script.

CREATE TABLE T1 (
    session_id INT
    ,status_message VARCHAR(1000) NULL
    ,login_name SYSNAME NULL
    ,Name_of_Host SYSNAME NULL
    ,Blocked_By SYSNAME NULL
    ,Database_Name SYSNAME NULL
    ,Script_description VARCHAR(1000) NULL
    ,CPU_Time INT NULL
    ,Disk_Input_Output INT NULL
    ,Last_Batch VARCHAR(1000) NULL
    ,Name_of_Program VARCHAR(1000) NULL
    ,Session_ID_2 INT
    ,ID_of_Request INT NULL
    ,Log_Date DATETIME DEFAULT GETDATE()
    );

Schedule and execute this script as a SQL Server Job. We can review the log table anytime to identify if our target database is being used.

INSERT INTO T1 (
    session_id
    ,status_message
    ,login_name
    ,Name_of_Host
    ,Blocked_By
    ,Database_Name
    ,Script_description
    ,CPU_Time
    ,Disk_Input_Output
    ,Last_Batch
    ,Name_of_Program
    ,Session_ID_2
    ,ID_of_Request
    )
EXECUTE sp_who2 active;

Method 2:

Unlike the above method, if you are not interested in too much of details and just wanted to know if the database is in use or not, then this script is the best fit. If you using SQL Server with version older than 2014, then this will not work. The output of this script shows 3 fields. The first fields gives info about the database name. This is the key field for us as this will help us to identify if our target database is in use. The second field will help us to classify the connections to the database as user connections vs SQL Server internal connections. The last column list the count of connections to the database.

SELECT DB_NAME(sys.dm_exec_sessions.database_id) AS [Database Name]
    ,CASE 
        WHEN sys.dm_exec_sessions.is_user_process = 1
            THEN 'YES'
        WHEN sys.dm_exec_sessions.is_user_process = 0
            THEN 'NO'
        END AS [Is it User connection?]
    ,COUNT(sys.dm_exec_sessions.session_id) AS [Connections Count]
FROM sys.dm_exec_sessions
GROUP BY DB_NAME(sys.dm_exec_sessions.database_id)
    ,sys.dm_exec_sessions.is_user_process
ORDER BY 1
    ,2;

Method 3:

Before pushing the data into the log table, in method 1, we cannot do a filter on database name. However it is possible in Method 2 and Method 3.

SELECT t1.objtype AS [Object]
    ,t1.refcounts AS [ReferredCount]
    ,t1.usecounts AS [Usage]
    ,t1.size_in_bytes / 1024 AS [KB Size]
    ,db_name(t3.dbid) AS [DatabaseName]
FROM sys.dm_exec_cached_plans t1
OUTER APPLY sys.dm_exec_text_query_plan(plan_handle, 0, - 1) t2
OUTER APPLY sys.dm_exec_sql_text(plan_handle) AS t3
WHERE db_name(t3.dbid) = 'ERP10_SandBox'
ORDER BY t1.usecounts DESC;

Method 4:

This method is very powerful in identifying database usage but it is very resource intensive. Yes, we are speaking about profiler. Pick a template from Profiler or use a custom template and track database connections.

Remove the database

From above listed methods, you will be able to identify if your database is still being used or not. If you come to a conclusion that it is not used anymore, the best practice is to inform the respective teams that it will be removed from the server. On a fine day, take a FULL backup of this unused database and drop it from the server.  Care must be taken not to corrupt SQL Server db during this process.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst data error and excel recovery software products. For more information visit www.datanumen.com