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.


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.



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.

| improve this answer | |
  • So if I enable successful and failed login audits, technically I will then have the information to see which databases are being accessed, when and by who? – rhughes Jul 15 '13 at 15:47
  • @rhughes not quiet true as that will tell you who is connecting to your database .. does not necessarily mean they are running SP's or queries ... Read up on server side trace and the events you want to trace, so that you know exactly who is connecting and doing stuff on your server - accessing tables, running SP's, etc. – Kin Shah Jul 15 '13 at 16:05
  • OK. I read on that page that you need to restart your SQL server after changing the audit levels. Did I read correctly? Can I setup a trace with no restart (I'm sure I can as I can on 2005+)? – rhughes Jul 15 '13 at 16:11
  • @rhughes I thing you are confusing 2 different things. If you enable Audit tracing then Yes you have to restart sql server, If you want to start server side trace, you don't have to restart sql server. – Kin Shah Jul 15 '13 at 16:37

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.