Is there anyway to tell when a database was last accessed or changed?

Is there a simple way to tell when a database was last accessed or changed?

We have some databases on old servers which we would like to discontinue but do not know if they are being used.

We would rather not just take them offline.

Is there anyway I can find out if they are still being used?

Please help if you can.

Regards, Major (that is my Christian name ;-)

I've run into this problem a few times. The best way I have found to deal with this is to set up a trace on the databases for reasonalbe period of time. With the trace, you should be able to see who accessed what, when, and with what application. This info can be helpful in determining how the database is being used in your organization.

The difficult part is to determine what constitutes a reasonable period of time. Some business groups access some databases on monthly, some on quarterly, and others on annual cycles. Other databases are used upon occurance of special events such as an audit or a lawsuit.

So, in addition to the trace, you may want to send out a survey asking for users to step forward. You will also want to be certain to take a few good backups and be able to access them very quickly should a user step forward after the database has been decommissioned.

Finally, get approval of higher-ups before dropping the database. This just helps spread the blame should someone in the business get upset.

Good luck,
Bryan

|||

Adding on to Bryan's comments, when the decision is finally made, I normally just take the databases 'off-line' for a period of time. Then if something breaks, it is quick and easy to put the database back online. (I've been know to keep databases in 'off-line' mode up to a year before completely removing them from the server.

Even with backups, when some upper level 'suit' is put out because the special use database isn't available and he must have a report in a hour, it is a boon to quickly revert to on-line status and say 'oops'.