This is a question we get as SQL Server DBAs all the time: is this database in use? Most of us are in the habit of putting this question back on the application owners, or of using a simple scream test to see if it’s important to anyone.
Until SQL Server 2008, there weren’t a lot of great options: triggers would work, but they have a lot of downsides, and they’re reasonably resource-intensive for busy servers.
Extended events offer a much better solution, and we’ll use this question as a way to ease into their use; this will be a beginner’s introduction to SQL Server extended events (on both SQL 2008 R2 and SQL 2012). We’ll end with a pretty cool (and portable) solution to the question, “is this database in use?”

TL;DR

If you want to skip to the scripts and whatnot, here you go. And check out Jonathan’s blog for tons of information.

Thanks

First, credit where credit is due: I attended Jonathan Kehayias’ PASS Summit session on extended events, and it was quite helpful. He’s really one of the go-to guys for this stuff. We’ll be using a fair bit from his post on tracking SQL Server database usage. The central pieces of the scripts we’re dealing with come from his post. We’re adding a bit on to it, and changing it up a bit for ease of deployment. You’ll definitely want to check out his blog posts on xevents for some great ideas and some real insight into how that system works.

Extended Events

Extended Events (or xevents) are the eventual replacement for SQL profiler, so you’ll want to start working on getting a grasp on them and how they work. In general, think of them as a very lightweight (much more so than profiler) set of processes for capturing information based on criteria.

Tools

If you’re working with SQL 2008 or SQL 2008 R2, you’ll want to use the SSMS extended events add-in. If you’re using SSMS 2012 (or later) to manage SQL 2008 systems, you’ll still need an add-in to get a GUI view of xevents on SQL 2008 environments. SQLSkills.com provides one, based on the above SQL 2008 add-in. Both are free. (Thanks, y’all!)

Introduction to extended events

The learning curve can be a little steep, so here’s a primer:
Extended Events are composed of:

Event – the triggering event you’re looking at
Action – the data you want to collect
Predicate – the “where” clause for your session: what needs to be the case before you’re interested in the data
Target – where you store the data you’ve gathered
Session – Think of a session as a trace: the session is composed of all of the other pieces above, and can be turned on and off at will. When the session is off, no data is gathered.

When you create an extended event session, you create the session, then add events, actions, a where clause (predicate), and target(s).

Database usage

In this case, we’re interested in whether a DB is being used by a user process (not a system process). All database use puts a shared lock on the database. As it happens, there’s an event for that: sqlserver.log_acquired.
So what we want to do is create a Session that looks at shared database lock Events. We want to collect the app name, username, and other details about the Action, Predicated on the lock being on a particular database and the user not being a system account.
Finally, we write the relevant data to a Target location.

The Script

Here we’ll look at it a piece at a time, then we’ll put it all together in a package that’s easy to deploy.

Session

First, let’s look at the session and event definition:

-- For SQL 2012
-- DatabaseUsage Event Session
CREATE EVENT SESSION [User_DBUsage] ON SERVER
ADD EVENT sqlserver.lock_acquired (    -- EVENT is when a lock is acquired
   SET collect_database_name = (1)     -- We want to get the DB name, too
   ACTION(sqlserver.client_app_name    -- and the app name
       , sqlserver.client_hostname     -- and the host name
       , sqlserver.database_id         
       , sqlserver.database_name
       , sqlserver.server_principal_name    -- the DB login used
       )

Some of the above details change a bit for SQL 2008; we’ll provide the full script for it below. Here’s how this runs down:

We create an session, and then add an event to that. Then we list the actions we want to look at. So far, this is stuff that’s not too hard to suss out; if we look at the GUI (in SQL 2012, here), we can see those listed out this way:

First we pick the event (lock_acquired) and move it to the selected events. Then we configure it:

and pick the actions we want to collect.
This is what the GUI looks like if you were to define (or edit) this session there.

We don’t want to stop there, though, because we haven’t defined any predicates. If we were to go with the definition above, by itself, we’d be gathering information about every connection to every database, all the time. We don’t want that at all.

Predicates

So we add our predicates:

-- For SQL 2012
-- DatabaseUsage Event Session
CREATE EVENT SESSION [User_DBUsage] ON SERVER
    ADD EVENT sqlserver.lock_acquired (        -- EVENT is when a lock is acquired   
        SET collect_database_name = (1)     -- We want to get the DB name, too   
    ACTION(sqlserver.client_app_name        -- and the app name
    , sqlserver.client_hostname             -- and the host name
    , sqlserver.database_id                
    , sqlserver.database_name       
    , sqlserver.server_principal_name        -- the DB login used
    ) 
    WHERE (
        [package0].[equal_uint64]([owner_type], (4))          -- This is a shared lock
        AND [package0].[equal_uint64]([resource_type], (2))   -- This is a DB lock
        -- AND [package0].[greater_than_uint64]([database_id], (4)) -- This would give info on all non-system DBs
        AND [package0].[equal_boolean]([sqlserver].[is_system], (0))  -- exclude system access
        AND database_id = 5 -- DB_ID of the target database
        AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[server_principal_name], N'DOMAIN\sqlSvcAcct') -- exclude svc account
        )
    )

Now we get into some stuff that is much less obvious
First, we see that there’s this equal_uint64 and equal_boolean kinds of stuff. Those are the xevent engine predicate comparison methods. These are predicate comparisons for predicate sources with different data types, which we can use in addition to our standard +, –, =, <, and >.
A couple of important details are here. First, we’re looking for a lock on a database with ID # 5. This will change in your environment, of course.
The second is the last line of the script. We want to exclude the operations not just of the SQL engine (DB startups, for instance), but also for maintenance tasks that occur in the context of the SQL Agent. To do this, we also tell it that we don’t want to include any locks taken out by the server principal of the SQL Agent login.
If you’re interested in the details of identifying the type of lock and the object of the lock (owner_type and resource_type, above), see Jonathan’s discussion of how the system stores that information.

Targets

Finally, we want to store this information somewhere that we can review it at a later date, so we add targets. In this example we’re adding two targets for demonstration purposes; you’ll likely only want to use one of these at a time, depending on your needs.

-- For SQL 2012
-- DatabaseUsage Event Session
CREATE EVENT SESSION [User_DBUsage] ON SERVER
    ADD EVENT sqlserver.lock_acquired (     -- EVENT is when a lock is acquired   
        SET collect_database_name = (1)     -- We want to get the DB name, too   
    ACTION(sqlserver.client_app_name        -- and the app name
    , sqlserver.client_hostname             -- and the host name
    , sqlserver.database_id                
    , sqlserver.database_name       
    , sqlserver.server_principal_name       -- the DB login used
    ) 
    WHERE (
        [package0].[equal_uint64]([owner_type], (4))           -- This is a shared lock
        AND [package0].[equal_uint64]([resource_type], (2))    -- This is a DB lock
        -- AND [package0].[greater_than_uint64]([database_id], (4))   -- This would give info on all non-system DBs
        AND [package0].[equal_boolean]([sqlserver].[is_system], (0))  -- exclude system access
        AND database_id = 5 -- DB_ID of the target database
        AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[server_principal_name], N'DOMAIN\sqlSvcAcct') -- exclude svc account
        )
    )
ADD TARGET package0.histogram (  -- This is like a bucket to drop info into
    SET filtering_event_name = N'sqlserver.lock_acquired'
        ,slots = (1) -- number of databases you're looking for
        ,source = N'database_id'
        ,source_type = (0) 

    )
,ADD TARGET package0.event_file (SET filename = 'c:\path\to\log_file.xel')        -- we're also saving more data to a file
    WITH (
            MAX_MEMORY = 4096 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 30 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = OFF
            ,STARTUP_STATE = OFF
            )

We’re first using the target that Jonathan included in his script, which is essentially a counter bucket. The histogram target allows us to keep track of the database and the number of connections that the xevent has recorded. This is fantastic for a quick-and-dirty look at whether the database has been used at all: If it doesn’t show up in the list, it isn’t in use.
We can query the data in this histogram target this way:

SELECT  slot.value('./@count', 'int') AS [Count] ,
        DB_NAME(slot.query('./value').value('.', 'int')) AS [Database]
FROM
(
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets AS t
    INNER JOIN sys.dm_xe_sessions AS s 
        ON t.event_session_address = s.address
    WHERE   s.name = 'User_DBUsage'
      AND t.target_name = 'histogram') AS tgt(target_data)
CROSS APPLY target_data.nodes('/HistogramTarget/Slot') AS bucket(slot)
ORDER BY slot.value('./@count', 'int') DESC;
 
GO

I like using this query because it’s very fast, and it gives me a nice summary. Here’s what it looks like:

Note that the count isn’t the number of times the database has been used; it’s the number of shared locks taken out since the session started (this is cleared when the session stops and when SQL Server restarts). Different operations will produce a different number of locks.
This *doesn’t*, however, tell us anything about the connections being made. So we’ve added another target to our session.
This is the file target, and this gives us a lot of the details we might want about who is using a database. It’s a binary file, however, so you can’t just open it in notepad. SSMS provide what I think is the easiest method: simply go to the File menu, select Open –> File and browse to the XEL file you created.

You’ll see something like this:

Cool, huh? Note that the database name, client application, client host, and login all are present, so not only do you know that the database is in use, but who is using it.

Wrap-up

Extended events are a great tool, and I think this is a nice introductory use case for them.
A couple of things to keep in mind:

Xevents are lightweight, but they’re not free. It doesn’t make a ton of sense to create a job like this one on a really busy database, unless you’re looking for a particular client workstation or login; in that case, you’d change the predicates to look only for that targeted information.
Because extended events are an evolving technology in SQL Server, there are and likely will continue to be changes to the events, actions, predicates, and targets available. This means you'll likely need to have version-specific xevent scripts around, depending on the SQL instance you're working on.
We'll be looking some more at basic extended events usage; do check out all that Jonathan Kehayias has done. I promise you'll be pleased with all he's got.

Deployment script

Here we've got a script that's set up with dynamic SQL to generate the extended event session for you. Note that it's not currently possible to use variables directly in a create session statement, so you have to generate the create statement dynamically, rather than just plug variables into it.

-- SQL 2012
IF EXISTS (
    SELECT event_session_id
        FROM sys.server_event_sessions
    WHERE NAME = 'User_DBUsage'
    )
    DROP EVENT SESSION [User_DBUsage]
    ON SERVER;
GO

-- Set the DB name below
DECLARE @dbid INT = (
        SELECT db_id ('databaseName')  -- <-- here
        );

-- Set the SQL Agent service account name below
DECLARE @svcacct VARCHAR(100) = 'DOMAIN\svcAccoutName';

-- set the log file path below
DECLARE @logfile varchar(300)='c:\path\to\log_file.xel';

-- ****** for routine deployment, no changes should be necessary below here *****
DECLARE @sqltext VARCHAR(max) = ' -- DatabaseUsage Event Session
CREATE EVENT SESSION [User_DBUsage] ON SERVER 
ADD EVENT sqlserver.lock_acquired (
    SET collect_database_name = (1)
    ACTION(sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_id
        , sqlserver.database_name
        , sqlserver.server_principal_name
    )
    WHERE (
        [package0].[equal_uint64]([owner_type], (4))    -- This is a SharedXactWorkspace lock
        AND [package0].[equal_uint64]([resource_type], (2))    -- this is a DB-level lock
        -- AND [package0].[greater_than_uint64]([database_id], (4))    -- this would exclude all system DBs
        AND [package0].[equal_boolean]([sqlserver].[is_system], (0))    -- exclude system access
        AND database_id = ' + convert(VARCHAR, @dbid) + '    -- the ID of the database we want to monitor
        AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[server_principal_name], N''' + @svcacct + ''')    -- exclude the SQL Agent service account from all this.
        )
    )         
    ADD TARGET package0.histogram (
        SET filtering_event_name = N''sqlserver.lock_acquired''
        ,slots = (1)  -- number of databases you''re looking for
        ,source = N''database_id''
        ,source_type = (0)    )
    ,ADD TARGET package0.event_file(
        SET filename='''+@logfile+''')
            WITH (
                MAX_MEMORY = 4096 KB
                ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
                ,MAX_DISPATCH_LATENCY = 30 SECONDS
                ,MAX_EVENT_SIZE = 0 KB
                ,MEMORY_PARTITION_MODE = NONE
                ,TRACK_CAUSALITY = OFF
                ,STARTUP_STATE = OFF
            )';
            
--print @sqltext;
EXEC (@sqltext);
GO

alter event session [User_DBUsage] on server state=start;
go

And SQL 2008 R2

-- SQL 2008 R2
IF EXISTS (
        SELECT event_session_id
        FROM sys.server_event_sessions
        WHERE NAME = 'User_DBUsage'
        )
    DROP EVENT SESSION [User_DBUsage]
        ON SERVER
GO

-- Set the DB name below
DECLARE @dbid INT = (
        SELECT db_id('DatabaseName')  --  <-- here
        );
-- Set the SQL Agent service account name below
DECLARE @svcacct VARCHAR(100) = 'DOMAIN\svcAccountName'

-- set the log file path below
DECLARE @logfile varchar(300)='c:\path\to\log_file.xel';


-- ****** for routine deployment, no changes should be necessary below here *****
DECLARE @sqltext VARCHAR(max) = ' -- DatabaseUsage Event Session
CREATE EVENT SESSION [User_DBUsage] ON SERVER
 ADD EVENT sqlserver.lock_acquired (
    ACTION(sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_id
        , [sqlserver].[nt_username]
        , sqlserver.username
        ) 
    WHERE (
        [package0].[equal_uint64]([owner_type], (4))
        AND [package0].[equal_uint64]([resource_type], (2))
        -- AND [package0].[greater_than_uint64]([database_id], (4))
        AND [package0].[equal_boolean]([sqlserver].[is_system], (0))
        AND database_id = ' + convert(VARCHAR, @dbid) + ' 
        AND [sqlserver].[not_equal_i_sql_unicode_string](sqlserver.nt_user, N''' + @svcacct + ''') 
        )
    ) 
    
    ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = N''sqlserver.lock_acquired'' 
    ,slots = (1)  -- number of databases you''re looking for
    ,source = N''database_id''
    ,source_type = (0)
    )
    ,ADD TARGET package0.asynchronous_file_target(
        SET filename='''+@logfile+''')
    WITH (
            MAX_MEMORY = 4096 KB
            ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
            ,MAX_DISPATCH_LATENCY = 30 SECONDS
            ,MAX_EVENT_SIZE = 0 KB
            ,MEMORY_PARTITION_MODE = NONE
            ,TRACK_CAUSALITY = OFF
            ,STARTUP_STATE = OFF
            )';

--print @sqltext;
EXEC (@sqltext);
GO

alter event session [User_DBUsage] on server state=start;

Wednesday, November 12, 2014

Who is using this database? Is it in use at all?