469,903 Members | 2,018 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

SQL Profiler

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
Jul 23 '05 #1
2 3822

"Akinja" <ak****@sbcglobal.net> wrote in message
news:13*****************@newssvr11.news.prodigy.co m...
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


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
Jul 23 '05 #2
Thanks, this is a good start.

Akinja
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:42********@news.bluewin.ch...

"Akinja" <ak****@sbcglobal.net> wrote in message
news:13*****************@newssvr11.news.prodigy.co m...
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


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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeff Mair | last post: by
8 posts views Thread by patrickshroads | last post: by
9 posts views Thread by Franco Gustavo | last post: by
3 posts views Thread by Maansi Gupta | last post: by
8 posts views Thread by Bryan | last post: by
1 post views Thread by ofirmgr | last post: by
3 posts views Thread by Sam Samson | last post: by
2 posts views Thread by Paul Ritchie | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.