• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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

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
goprasad
Asked:
goprasad
1 Solution
 
goprasadAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now