Determine date Database was last used ?

Hi There

I am trying to establish the date a database was last used.

At first i checked out sp_helpfile and sysfiles to see if there was a date last modified, i also tried using xp_cmdshell to check the date on the actual .mdf or .ldf on the o/s but if the file has not grown the date will be the create date.

I need to establish the date a database was last used, either the last time someone logged into it or the last time any sort of command was run against the database, i cannot use sysprocesses as there are no connections to the database, so i need to determine the last time there actually were any processes run against this database.

And i need to use tsql, checking audit logs etc is not viable, can anyone help ?

Thanx

There is no easy way to get this information. The database engine doesn't keep track of these type of statistics. You could create a profiler trace that tracks the login/logout events and filters on the database column to get a list of connections. But this will not track cases where someone connects to a different database and then does a USE database to switch context. Alternatively, you could set the auto close database option. This will force the database to be closed automatically when there are no connections. When an auto-closed database is opened when a new connection tries to access it you will see a message in the SQL error log. So you could search for these messages to find out when the database was last opened. This of course requires that you retain your error logs. Note that using auto close has performance implications so you should be careful about using it. So what is the reason for trying to determine when a database was last used? What do you want to do with that information?|||

i think you can make use of third pary tools. "lumigent log explorer"

if youre into auditing logins alone. maybe you should turn on the C2 auditing mechanism of sql server

|||

Hi Umachanda

I had a feeling this would be the case, it was a bit of long shot.

I have to consolidate over 200 databases for a large company, unfortunately they have not had a proper DBA, also no one really knows their sql environment, too simplify the task i thought i should first compile a list of databases still in use to be consolidated. As i have been told that some of the databases are probably no longer used, howver no one can accurately provide me with a list so i was hpoing to somehow determine databases that had not been used in say the last 3 months.

But i can see this is not going to be an easy task.

Thank You for the feedback anyway.

Cheers

|||It looks like you can go with the auto-close approach although it has some performance impact. You could try setting this for the database that you don't know for sure are used by some application. This will at least limit your scope to a subset of the 200 databases. As suggested, you can take a look at 3rd party tools also but I am not sure if they have a canned mechanism to answer these type of questions and it will probably cost a lot more to solve this problem.|||Cool Thanx Umachandar