Finding login or user name of last person to use a DB

by Paul   Last Updated July 07, 2017 19:06 PM

The boss has asked for a list of DB names along with size and name of last person to use that DB. I've found resources for when a DB was last accessed but for by whom. How would I solve this?

SQL2008R2 being used and so far I have this:

exec sp_MSForEachDB '
use ?
select ''?'', (SUM(df.size)*8)/1024 as ''Size (MB)''
from sys.database_files as df

It is acceptable for the last person aspect of the answer to be fuzzy or loose. i.e. as long as it's someone who went near it sometime vaguely recently that is "OK".

Answers 1

If your server has been up for a reliable amount of time (e.g. at least one business cycle), you can tell when there has been any read or write against any table in a database using DMVs like sys.dm_db_index_usage_stats.

There isn't really a reliable way to tell who last "used" a database. SQL Server doesn't log this information unless they do something substantial (e.g. you might see that a user created or dropped an object in the default trace, but this will by no means tell you they were the last person to access the database, or if that was the last time they accessed the database).

For the database file sizes, you don't really need to use sp_MSforeachdb at all; and you shouldn't use that method anyway. Here's why:

In this case, you don't need to loop through all of the databases anyway; this information is replicated in the view master.sys.master_files.

  [Size (MB)] = SUM(mf.size)*8/1024
FROM master.sys.databases AS d
INNER JOIN master.sys.master_files AS mf
ON d.database_id = mf.database_id

If you have to do something through a bunch of databases that isn't supported by something in master, I'd rather do this type of technique than use sp_MSforeachdb:

SELECT @sql += N'SELECT ''' + name + ''', (SUM(df.size)*8)/1024 as [Size (MB)]
  from ' + QUOTENAME(name) + '.sys.database_files as df;';
EXEC sp_executesql @sql;

(As an aside, don't use 'single quotes' to delimit an alias, use [square brackets]. The former is deprecated in some forms - see here and here - and makes your alias look like a string literal anyway.)

For the "when" part, copying from my answer here:

SQL Server doesn't really track database access in the way you want, at least going backward (you can set up things like server-side trace, extended events, auditing, etc. going forward).

There is one ballpark thing you can use: DMVs that track index usage and procedure/trigger/query stats. For example:

  SELECT d = database_id, name FROM sys.databases
  WHERE state = 0 AND database_id BETWEEN 5 AND 32766
index_usage(d,lsk,lsc,llk,lupd) AS
  SELECT database_id, MAX(last_user_seek), MAX(last_user_scan),
    MAX(last_user_lookup), MAX(last_user_update)
  FROM sys.dm_db_index_usage_stats
  WHERE database_id BETWEEN 5 AND 32766
  GROUP BY database_id
proc_stats(d,lproc) AS
  SELECT database_id, MAX(last_execution_time) 
    FROM sys.dm_exec_procedure_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
trig_stats(d,ltrig) AS
  SELECT database_id, MAX(last_execution_time)
    FROM sys.dm_exec_trigger_stats
    WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
query_stats(d,lquery) AS
  SELECT t.[dbid], MAX(s.last_execution_time) 
    FROM sys.dm_exec_query_stats AS s
    CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
    WHERE t.[dbid] BETWEEN 5 AND 32766
    GROUP BY t.[dbid]
FROM d LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats  AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats  AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d;

Note that these statistics aren't completely reliable, since you may not have any stored procedures, and the queries found in sys.dm_exec_query_stats may reference more than one database, and may not ever reflect the one you are concerned about.

Also, they are reset when SQL Server is restarted, or a database is detached / attached or restored, or when a database is auto-closed, and can also depend in some cases on the plans still being in the cache (which another database could completely take over within minutes). So if you are looking into the past, unless you know none of these things have happened for an entire business cycle, I wouldn't rely on just these numbers to determine whether a database is used (there could also be automated processes that are making a database look current, even if you don't care that those automated processes will fail when you remove the database).

Another note is that certain index access may not be tracked in the index usage views; for example, in SQL Server 2014, which adds memory-optimized tables, activity against those hash indexes are not captured this way (and the views where you think the activity would be captured, like sys.dm_db_xtp_hash_index_stats, do not include any date/time columns). If you are using SQL Server 2014 and in-memory OLTP ("Hekaton"), you may want to add some research to cover those objects (in case they are the only ones being referenced in a database).

And one more note is that the queries captured by sys.dm_exec_query_stats could be false positive. For example, if your database has filestream/filetable, you will see these queries being run by the system occasionally:

select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [database].[sys].[filetable_updates_<some_id>] with (readpast) order by table_id

So, you may want to add additional filtering to the above query to filter those out (as long as the filter doesn't accidentally filter out queries you do care about). This is probably a safe addition to that derived table:

AND t.[text] NOT LIKE N'%oplsn_fseqno%'

In the end, the safest thing to do in a dev environment is to take the databases you're not sure about offline for a week. If nobody complains, back them up, and drop them. If it takes more than a week for someone to notice they're missing, you can always restore them (there or elsewhere).

I've blogged about this a bit as well:

Aaron Bertrand
Aaron Bertrand
April 08, 2014 13:58 PM

Related Questions

Full text catalog missing a keyword

Updated August 23, 2017 13:06 PM

MSDTC Issue SQL Server 2008 R2

Updated August 23, 2016 08:04 AM