Solved

SQL Profiler trace to capture all transactions/queries for all the DB's in the SQL 2005 instance

Posted on 2013-11-11
2
565 Views
Last Modified: 2013-11-18
Hello there,

We are in the process of identifying the DB's that are currently active OR in use. I was thinking if using SQL profiler to run a trace.
As I have 40 DB's in the instance, how should I go about selecting all these DB's so that I can check if indeed there are any transdaction happening.
In this we know which DB's are currently active.

Is my understanding correct? If so how should I go about setting up trace.

Please advise.

Thanks and Regards
0
Comment
Question by:goprasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Author Comment

by:goprasad
ID: 39640810
OR can i use the following query, will this be source of truth:

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses
WHERE dbid > 0GROUP BY dbid, loginame

Please advise.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39641012
Querying sys.sysprocesses will only give you a point-in-time view (i.e. will only tell you if anybody is connected at that exact moment).

Personally I would run a trace on the "Audit Schema Object Access Event" class (under the "Security Audit Event" section) and a a filter on the DatabaseID column to only show anything with an ID > 4.

You'll need to run it for a period of time that would cover a reasonable window for when you would expect people to access any of the databases.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question