December 10, 2013 at 5:26 pm
Hello, i've been trying to find out user access history on a particular DB. I used a query which returns the last accessed date of the database however the problem is that it doesnt specify who the user was and also only returns information from the last reboot onwards.
What i'd like to get is at least the last 3-5 times the DB was accessed and by whom. This would allow me to determine whether the DB is still in use or whether I can decommission it. Any assistance is greatly appreciated. fyi the query i've been using is listed below. Thanks
SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
(SELECT DB_NAME(database_id) DatabaseName
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT (LastAccessDate FOR last_user_access IN
(last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update)
) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName IN ('testdb')
ORDER BY 2
December 11, 2013 at 3:35 am
Unless you've set up extended events (or a server-side trace) to capture that type of information, there's no way to get it just by querying SQL Server. It's not stored in a meaningful way within the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply