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:StmtComplete d, SP:StmtStarting
TSQL - SQL Batch completed, SQL:StmtComplet ed
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
2  3965 
"Akinja" <ak****@sbcglob al.net> wrote in message
news:13******** *********@newss vr11.news.prodi gy.com... 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:StmtComplete d, SP:StmtStarting
TSQL - SQL Batch completed, SQL:StmtComplet ed
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
Thanks, this is a good start.
Akinja
"Simon Hayes" <sq*@hayes.ch > wrote in message
news:42******** @news.bluewin.c h...
"Akinja" <ak****@sbcglob al.net> wrote in message
news:13******** *********@newss vr11.news.prodi gy.com... 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:StmtComplete d, SP:StmtStarting
TSQL - SQL Batch completed, SQL:StmtComplet ed
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Jeff Mair |
last post by:
Hello all,
I haven't been using profiler for a terribly long time, and I've come across
something that I just can't figure out. I'm running MS Virtual PC to run a
Win2K Server OS with SQL Server 2000 (sp3 i think). Whenever I start up SQL
Profiler - even immediately after a bootup, if I try to pause, stop, or exit
out of profiler, it hangs. The strange part is that it seems to keep on
tracing even though it won't respond anymore. I...
|
by: patrickshroads |
last post by:
I am running a profiler trace against a database and noticed that the
reads column always shows 0. When running the same trace against
another machine I get back values in the reads column. I took a query
that profiler reported as having 0 reads and ran in in query analyzer
wtih STATISTICS IO on and confirmed that there are in fact reads:
Table 'tt_cawardalloc'. Scan count 1, logical reads 8, physical reads
0, read-ahead reads 1.
Table...
|
by: DraguVaso |
last post by:
Hi,
I'm using for a while DevPartner Profiler
(http://www.compuware.com/products/devpartner/resources/profiler/profiler.as
p), but unfortunately it works only for 2002 and 2003.
Is there a profiler that looks like DevPartner Profiler (gives the same
information: shows clearly which methods/statements takes the most time in
total etc and free!). Or will DevPartner make a version for 2005 soon?
|
by: Franco Gustavo |
last post by:
Hi, All
What's is the best .net profiler for Debug an application. I have to buy one
but I don't know which one!!
I tried some of them like Borland and AQTime.
My big problem is, my application is a big application, and many times I
have many EventHandlers associated to an object, then this object never is
free from the memory.
AQTime for .Net can tell me how many references of EventHandler the object
|
by: Maansi Gupta |
last post by:
Platform - Windows 2000 Professional , VC .NET 7.1
Hello,
I have recently ported my source code from Visual Studio 6.0 to .NET 7.1
Previously with Visual Studio 6.0 I was using the profiler in the following
way
1) Build the project with link setting- Enable Profiling as checked.
2) I had created a custom batch file for profiling my application and was
| | |
by: Bryan |
last post by:
Does anyone have an example of an application that can connect to a running
process and capture Trace.WriteLine calls like in SQL Server Profiler? I
know that we can inherit from a TraceListener class to write to files,
databases, etc..., but I would really like to attach to running .NET
processes and get the results without any logging and instead capture the
real-time results when troubleshooting.
|
by: ofirmgr |
last post by:
im trying to understand how the profiler works. so i started 2
profilers,one listen to another and I saw the profiler is running:
exec sp_trace_create @P1 output, 1, NULL, NULL, NULL
which means @tracefile = NULL
so where from the profiler read the results?!
|
by: Sam Samson |
last post by:
Can any-one recommend some good sites detailing how to code a simple memory
profiler?
How do they "look under the hood"?
I built an uber simple service that tracks the process' memory but I need
to "get inside" and look a bit deeper and I don't have a clue where to start
... a commercial memory profiler is not an option due the nature of the environment
the application is deployed in ..
My app runs fine for weeks (in 8 locations) but...
|
by: Paul Ritchie |
last post by:
A few years ago I used a Profiler (I forget the name) on my Delphi code at
the time and got fantastic results showing both:
a) lines of code that took the longest to execute and
b) lines of code that allocated the most unreleased memory.
I have looked around and am unable to find a good tool to easily achieve
these statistics for my C# Winforms application. Possible reasons are that:
1) I am having difficulty finding the existing...
|
by: anweshadash |
last post by:
Using SQL Profiler:
(Ref: http://msdn2.microsoft.com/en-us/library/ms181091.aspx)
Sometimes it’s very beneficial to use sql profiler.
Let’s have an idea about that and how to use that by taking some scenarios.
Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the SQL Server Database Engine or Analysis Services. You can capture and save data about each event to a file or table to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
