February 16, 2012 at 9:23 am
I am trying to find when the table was accessed last and I am able to do that using the index stats
However, I need to find the login that accessed the table last.
I am using the following scripts ( using SQL Server 2008 R2)
select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()
Any help wpuld be greatly appreciated. Thanks
February 16, 2012 at 10:05 am
It's no so easy-
February 16, 2012 at 10:16 am
Unless you have custom auditing or something like SQLAudit, there is no way to retrieve that information. SQL just doesn't keep it by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2012 at 12:16 am
If you decide to add auditing, keep in mind the overhead this is going to create. Aaron's article is great, but read the update.
UPDATE - Mike C# and dave ballantyne brought up a great point that applies to all DMVs: the values do not survive a SQL Server restart, or detach/attach, or even Auto-Close. So, if you restart your server and then want to see when something was last accessed, all objects will either be NULL or very recent. One way to work around this is to create a SQL Server Agent job that polls the DMV periodically, and stores a snapshot of the data. This way you can have a running history of "last access" and maybe roll it up once per day (or whatever granularity is suitable).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply