Database last updated?

Question

I'm working with SQL 2000 and I need to determine which of these databases are actually being used.

Is there a SQL script I can used to tell me the last time a database was updated? Read? Etc?

I Googled it, but came up empty.

Answer 1

You can use a transaction log reader to check when data in a database was last modified.

With SQL 2000, I do not know of a way to know when the data was read.

What you can do is to put a trigger on the login to the database and track when the login is successful and track associated variables to find out who / what application is using the DB.

Answer 2

Edit: the following targets issue of finding, post-facto, the last access date. With regards to figuring out who is using which databases, this can definitively monitored with the right filters in the SQL profiler. Beware however that profiler traces can get quite big (and hence slow/hard to analyze) when the filters are not adequate.

Changes to the database schema, i.e. addition of table, columns, triggers and other such objects typically leaves "dated" tracks in the system tables/views (can provide more detail about that if need be).

However, and unless the data itself includes timestamps of sorts, there are typically very few sure-fire ways of knowing when data was changed, unless the recovery model involves keeping all such changes to the Log. In that case you need some tools to "decompile" the log data...

With regards to detecting "read" activity... A tough one. There may be some computer-forensic like tricks, but again, no easy solution I'm afraid (beyond the ability to see in server activity the very last query for all still active connections; obviously a very transient thing ;-) )

Answer 3

If your database is fully logged, create a new transaction log backup, and check it's size. The log backup will have a fixed small lengh, when there were no changes made to the database since the previous transaction log backup has been made, and it will be larger in case there were changes.

This is not a very exact method, but it can be easily checked, and might work for you.

Answer 4

I typically run the profiler if I suspect the database is actually used. If there is no activity, then simply set it to read-only or offline.