Hi

I am just started at a new position. This organization has a number of
database servers with mission critical databases. However, I have two
database server, 1 a test server and the other a catch all db server that
contain a hodge podge of databases. On the test server, there are 54
databases while the other has 40. I am pretty confindent that not all of
these databases are being used. My goal is to find out which ones are no
longer needed.

In an attempt to find out who/what is using these databases, I have set up
SQL Profiler.
My SQL profile setup is as such
Objects - Objects opened
Security Audit - Audit Login, Login Failed, Logout
Session - Existing Connection
Stored Proc - RPC Completed, SP:StmtCompleted, SP:StmtStarting
TSQL - SQL Batch completed, SQL:StmtCompleted

My data columns are the standard except I added DatabaseName.

And there lies my problem that I hope someone can help. As I run the trace,
all other fields seems to be working except Database Name. I see everything
but the only way to tell what database is being accessed is looking at the
SPID number and comparing it with Process Info in EM or sp_who.

Does anyone have any idea why this field is not working? Am I missing
something? Or am I just giving myself a headache for nothing because there
is an easier way to find the information I want: What the heck is going on
with these database and who is doing what on them?

Thanks in advance for any and all help

Akinja

"Akinja" <akinja (AT) sbcglobal (DOT) net> wrote

The DatabaseName field is not populated for many (perhaps most) events.
Check out "Monitoring with SQL Profiler Event Categories" in Books Online,
which lists the data columns which are filled for each type for event. If
you audit adding or removing a database user, for example, you'll see that
the DatabaseName does appear.

Despite that, it seems that the DatabaseID column is always populated, so
you could use that instead - it might be easier than working with the SPID.
This doesn't make a lot of sense to me, but presumably it did to someone in
the Profiler development team.

Simon

Thanks, this is a good start.

Akinja


"Simon Hayes" <sql (AT) hayes (DOT) ch> wrote