Monitoring db activity/usage in SQL Server 2005

[ArmatageShanks]

Is it possible to find out
a) how often, and
b) when last
a sql server 2005 database is/was accessed?

I need to write a script that will determine which dbs are being used and which have just been lying dormant.

[hamin_aus]

Quick and dirty solution would be to use SQL Server Profiler to run a trace to do this.

You can filter the Audit Login, DatabaseName and Application events plus anything else you may think you need, you weren't very specific...

Let the trace run for a day or a week - whatever you want.
Save it to some table somewhere that you can run custom queries on afterwards to see how often each DB was accesses etc etc.

Thats what I'd do off the top of my head, I'm sure there is a better way to do it than a trace, but this should be the quickest.

[Ron2K]

Running a trace is pretty much the only way I know how to do this.

[ArmatageShanks]

Ok thanks; will look more into that.

[ArmatageShanks]

Ok, so I've started playing around with the profiler. What would be the best event to trace to monitor db activity? I was thinking perhaps SQL:BatchCompleted, because it records all queries made to all db's on a server; but letting it run for a week or two could result in a very big saved file/table indeed.

[hamin_aus]

Do you want to record unique accesses like logins, or do you want to know every time a db is accessed for a query or some other call - even if that is multiple times by the same login?

[ArmatageShanks]

All I need to know is if the database is being used at all (for every database on a server).
I just need the most efficient way of determining this.