Is That Database in Use?

Does anybody know who or what is using this database? Better yet, is the database being used by anything at all? Have you ever asked those types of questions of your staff in an environment you have inherited?

As silly as it might sound, these are legitimate questions. It is not uncommon to have a relic database hanging around after an application has long since been retired. The database just continues to live on via life support in the dark recesses of your database server. Everybody has forgotten about it yet it consumes precious resources to do little more than exist.

But how do you go about discovering if the database is truly required or if it is just chewing up space and cpu cycles? The answer is to start by discovering if there is any activity in the database at all. Extended Events is an ideal method to audit your database and discover if there is truly activity on the database or not.

DB In Use?

Depending on the version of SQL Server you are using, the method is going to be a little bit different. Each method I use still relies on Extended Events (XE), they just require some slight changes and subtle nuances (particularly for those pesky 2008 and R2 instances out there). Let’s focus on the 2008 method this time around and we will revisit the updated version in a later article.

2008/R2

Let’s first start with the XE session that works well for discovering the frequency of use a database may be encountering.

/* 2008R2 */USE master;
GO
-- Create the Event Session
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'DBinUse')
DROP EVENT SESSION DBinUse ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
GO
CREATE EVENT SESSION DBinUse
ON SERVER
ADD EVENT sqlserver.database_transaction_begin
( --Occurs when a database transaction begins. Count of transactions at the database level that "start"
 ACTION
 (
 sqlserver.database_id
   , sqlserver.database_context --action is deprecated in later versions
 )
)
ADD TARGET package0.asynchronous_bucketizer
(SET source_type = 1, source = N'sqlserver.database_id') --2008r2 requires that the package name be specified with the action
WITH
(
MAX_DISPATCH_LATENCY = 5 SECONDS
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = ON
);

/* start the session */ALTER EVENT SESSION DBinUse ON SERVER STATE = START;
GO

Inline with the script, I left some interesting notes. First, the note in the target section is of particular interest because it lays out a requirement for this kind of target in 2008/R2. When using the action source type, then the package name must be specified along with the action in two-part naming format.

Next, in the actions, I have a note about sqlserver.database_context. This particular action is deprecated in later versions. Do I need it in this session? No. I put it in there for additional troubleshooting/exploration.

Lastly, I have a note about the event name. I chose database_transaction_begin because this is fired when a database transaction starts. I don’t really care if the transaction completes. I just want to know if an attempt was made to use that database and the data therein.

If I comment out the deprecated action (database_context), I could actually use this session on later versions (as constructed) of SQL Server. It doesn’t matter that the asynchronous_bucketizer has been replaced by the histogram target, the session will still create and work properly. SQL Server knows to automatically update the deprecated targets with the appropriate target when creating an XE Session on a server.

Since this is 2008/R2, we have to use TSQL in order to parse the data. The following query will do that for us!

WITH dbusepreprocess AS
(SELECT
 CAST([target_data] AS XML) AS target_data
   , xes.event_name
 --INTO #xmlpreprocess
 FROM sys.dm_xe_session_targets AS xt
 INNER JOIN sys.dm_xe_sessions AS xs
 ON xs.address = xt.event_session_address
 INNER JOIN sys.dm_xe_session_events xes
 ON xes.event_session_address = xt.event_session_address
 WHERE xs.name = N'DBInUse'
   AND xt.target_name = N'asynchronous_bucketizer')
SELECT
d.name AS DBName
  , summary.dbuse_count
  , summary.event_name
FROM
(
SELECT
hist.target_data.value('(value)[1]', 'bigint')AS database_id
  , hist.target_data.value('(@count)[1]', 'bigint') AS dbuse_count
  , pp.event_name
FROM dbusepreprocess   pp
CROSS APPLY target_data.nodes('BucketizerTarget/Slot') AS hist(target_data)
) summary
RIGHT OUTER JOIN sys.databases d
ON summary.database_id = d.database_id;

After executing that query, I will see results listing the activity of each database since the session was started. Here’s an example.

Here we can see there are indeed some databases that are still in use on this server. If we are looking to retire the instance, or migrate relevant databases to a new server, we have better information about how to go about planning that work. Based on this data, I would be able to retire the ProtossZealot database but would be required to do something with the ZergRush and BroodWar databases. In the case of the ProtossZealot database that is not being used, we now have evidence to present back to the team that the database is not used. It is now up to those insisting on keeping it around to justify its existence and document why it must remain in tact.

Wrapping it Up

Extended Events is a powerful tool with plenty of ease of use and flexibility. This flexibility allows the DBA to capably monitor the server for any issue be it small or large. This article demonstrates how to use Extended Events to determine if a database is being used by someone or something. If you are in need of a little tune-up for your XE skiils, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.

In addition, through the power of XE, we were able to capture a quick glimpse into some internals related to the database engine. In this case, we were able to see autoshrink in action and discover the frequency that it runs on when enabled.