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".
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
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
SELECT d.name, [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 GROUP BY d.name;
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
DECLARE @sql NVARCHAR(MAX) = N''; 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:
;WITH d AS ( 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] ) SELECT d.name,i.lsk,i.lsc,i.llk,i.lupd,p.lproc,t.ltrig,q.lquery 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
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: