database access history

I was asked to determine the last time 2 databases were accessed.

Are the .trc files an accurate way to determine the database access history?

July 31st, 2015 9:21am

As of now there is no feature for this. MS developers were planning to include a column in sys.databases for this but dropped that idea as it was not practically possible. Anyway coming back to the workaround for you is to create an audit (SQL Server 2008) or DMV for Index (SQL Server 2005)

There is a blog from Aaron on this.

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

http://dinesql.blogspot.com/2010/05/when-was-my-sql-server-database-last.html

Free Windows Admin Tool Kit Click here and download it now
July 31st, 2015 9:51am

Hello - SQL Server Trace files are not the full-proof way of capturing this information however they indeed are useful and may help with your requirement provided events such as Back-ups, Restores, DBCC & ALTER, CREATE, DROP got executed on this database

If you are seeing those events then you would know when, who, where from these trace files but things like those did not happen then those files are not useful considering that users/applications only performed standard things such as Insert, Update or Delete which are never captured in default trace files.

Additionally I would also advise you to configure & use SQL Server Audit to capture events, they are better way of knowing what you need to know

Hope this helps !

July 31st, 2015 10:19am

thanks
Free Windows Admin Tool Kit Click here and download it now
July 31st, 2015 2:10pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics