Database Last Usage

Posted on

Question :

Is there a way to find out when a database was last accessed on SQL Server 2000? By this I mean, had a table read, a stored procedure called etc…

I have a list of databases and I am trying to find out which databases are still needed or not.

I’m not sure sp_who2.LastBatch will give me the result I need.

Answer :

There is no way in SQL 2000 – as it does not support any DMV’s introduced in sql server 2005.

Your best bet is to use an AUDIT trace or run server side trace.

Note: depending on what you are capturing, it will be resource intensive and might end up filling up your disk space as well.

EDIT:

@rhughes

Refer to this para in the link :

To achieve a fine-grained approach to these types of auditing, you’ll need to turn to Server-Side Traces. SQL Server has long provided the ability to trace activity for debugging and performance-monitoring purposes. You can also use Server-Side Traces to monitor security-related activity. Unlike C2 auditing, traces aren’t persistent. If you restart SQL Server, the trace is lost and you must recreate it. Another difference between C2 auditing and traces is that you can start and stop a trace without restarting the database service. Traces are extremely configurable; you can choose exactly which events to audit and what information about each event to record.

I’ve successfully set up a trace on SQL 2000 where I use a job that every hour starts a new trace, then ends the old trace (started by the job that ran an hour ago). Once the old trace is ended it then processes the data into a summarized table. Using that methodology I’ve got traces that tell me when the last time any given object was touched, how many times a given login logged in or out in a given hour etc.

I will say it is somewhat space intensive, and somewhat resource intensive at times, but it does work.

Leave a Reply

Your email address will not be published.