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

Posted on 2013-11-11
Medium Priority
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
Question by:goprasad

Author Comment

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.
LVL 52

Accepted Solution

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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

627 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