How to find when database was last accessed or modified?
Posted on 2006-06-07
For the purpose of backup audits, I can find when the last backup was done on our numerous distributed SQLServer databases. The missing piece is whether the database has been modified since last backup. We have archive databases which do not change, so I wouldn't spend a lot of worry if it hadn't been backed up in months as long as that date was after last modify date/time.
I have tried using the "xp_getfiledetails", but this does not correlate with the last time the database had a row updated or inserted, etc.
sysdatabases doesn't contain a date that correlates with such updates either.
All of our SQLServers are running at version 8 sp3a. SQL2005 is still a ways off for the majority of our current databases.
Any help would be appreciated.