[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
582 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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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