How to know when was a database last used?

Question

How to know when was a database last used?

Ricardo Del Campo

Mr or Mrs. 500

Points: 533
More actions
August 15, 2007 at 9:18 am

#118124

Hello,
I need to create a job to notify that certain database has not been used for a predefined amount of time. This is for a development server where several databases are created and sometimes they're never dropped after last use. How can I come up with a list of databases that have not been used in the last, for example, 15 days?
Thanks a lot in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bob Fazio SSChampion Points: 10794 More actions · Answer 1

I think you will have to define used in this case.

Just a login? If that is sufficient you could modify server to audit successful logins, but that won't tell you what database they accessed.

You can look to see if an object has been altered, but normal maintanence can cause that to happen.

Suggestion. Offline all databases (minus some key ones) every weekend. If they don't online them for a period of time, drop. (offline and online operations are logged in event log).

Ricardo Del Campo Mr or Mrs. 500 Points: 533 More actions · Answer 2

Thanks for the replay.

I was thinking in regular users usage, not the one caused by posible maintenance job or DTS. I checked on sysusers table but it doesn't show when was the last time a user accessed the db. In the other hand, master..sysprocesses gives me when was the last batch run in any database for the users that are currently connected, but it doesn't keep history.

I guess I'll have to keep digging.

Thanks again.

PD: Unfortunatelly, make them offline is not an option in this case. There's a lot of users that only have basic privileges (datareader, etc..), so they would not be able to put them back online.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Can you create a small app that will execute as SA (or any minumum privilege to put it back online)? MS Access will most likely be enough as they only need a list of all offline DBs, then a button to put it back online... and Office is usually installed on most pcs.