Finding Unused Databases on Your SQL Instance

We’ve been re-arranging some deck chairs at work (deploying a new backup tool in our SQL environment). As we’ve been working our way through that we’ve had to update or re-engineer some of our processes for automatic restores to use the new tool. We’re doing one restore though where no one could recall who actually used the restored database. After asking around for a month we didn’t have any better idea of whether the database was actually used or not. Being a risk-averse (not to mention polite) group we wanted to really be sure that no one was using it. Thus the inspiration for this post was born: we needed a way to audit database access confidently say one way or the other that our mystery database was in use or not.

We needed something quick and lightweight and After a quick spin through the SQLHelp channel on Slack I’d been pointed in the direction of extended events. SQL combined with XML my forever nemesis. Thanks to some help from Thomas Schutte (b) particularly his blog post on getting started with XML in SQL. So here is some useful code if you need to audit database use on a server.

First we need a simple extended event session. I picked the sql_batch_completed event so I knew I would catch users as they changed database context on the server and executed code. I kept the data just in the ring buffer since I’ll be checking it often to pull out relevant data and storing it in a table.

CREATE EVENT SESSION [UserContextAudit] 
  ON SERVER ADD EVENT sqlserver.sql_batch_completed
    ( ACTION 
      ( sqlserver.database_id , sqlserver.database_name , sqlserver.session_id , sqlserver.session_nt_username , sqlserver.sql_text , sqlserver.username ) 
    WHERE sqlserver.session_id > 100 ) 
ADD TARGET package0.ring_buffer WITH ( MAX_MEMORY = 1024KB , STARTUP_STATE = ON );
GO

ALTER EVENT SESSION [UserContextAudit] ON SERVER STATE = START;

Generating extended events is easy peasy, especially when using the wizard in SSMS. The terrible (for me) part is parsing the results from XE which is stored as XML. I got about 65% of the way there but struggled to get to the data points I needed out of the XML. Thomas’s post above pointed me in the direction of the second cross apply I needed to get the XML nodes I was missing into my temp table. Once you have a good query to pull out the extract the data from the XE session we just dumb the results into our database. I’m running this script once a minute via a SQL Agent job to preserve the information.

SET QUOTED_IDENTIFIER ON;

DECLARE @XML AS XML;

SELECT @XML = (
        SELECT TOP (1) CAST(xet.target_data AS XML) AS XEData 
        FROM sys.dm_xe_session_targets AS xet 
          JOIN sys.dm_xe_sessions AS xe ON xe.address = xet.event_session_address
        WHERE xe.name = 'UserContextAudit' AND xet.target_name = 'ring_buffer'
              );

SELECT ce.event_node.value('@timestamp [1]', 'DATETIME2') AS EventDate 
    , ca.action_node.value('@name [1]', 'VARCHAR(200)') AS ActionName
    , ca.action_node.value('(value/text())[1]', 'VARCHAR(MAX)') AS ActionValue 
INTO #temp 
FROM ( VALUES ( @XML )) xx ( XMLData ) 
    CROSS APPLY xx.XMLData.nodes('//RingBufferTarget/event') ce(event_node)
    CROSS APPLY ce.event_node.nodes('./action') ca(action_node);

SELECT DISTINCT a.EventDate 
    , a.ActionValue AS username 
    , b.ActionValue AS databasename 
INTO #temp2 
FROM #temp AS a 
    JOIN #temp AS b ON b.EventDate = a.EventDate 
    JOIN #temp AS c ON c.EventDate = b.EventDate 
WHERE a.ActionName = 'username' 
    AND b.ActionName = 'database_name' 
    AND c.ActionName = 'sql_text';

UPDATE upd 
SET lastdate = d.EventDate 
FROM AuditDB.audit.usertracking AS upd 
  JOIN #temp2 AS d ON d.username = upd.username 
    AND upd.databasename = d.databasename
WHERE upd.lastdate < d.EventDate;

INSERT INTO AuditDB.audit.usertracking ( username , databasename , lastdate ) 
SELECT username
    , databasename
    , MIN(EventDate) 
FROM #temp2 AS i 
WHERE NOT EXISTS (  SELECT * 
                    FROM Audit.audit.usertracking a 
                    WHERE a.username = i.username 
                        AND a.databasename = i.databasename ) 
GROUP BY username, databasename;

DROP TABLE #temp;
DROP TABLE #temp2;

Run that XE Session and SQL Agent collector job for a month or so and you should have a solid idea of what databases are actually being utilized on your server. There is a non-zero chance that a database might only be referenced as cross-db query but if you think that’s possible the sql_text from the XE session could be analyzed to look for and pull apart 3 part names for databases referenced this way.