Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2005 (Trace Template to capture all queries) Expand / Collapse
Author
Message
Posted Thursday, July 1, 2010 7:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
What trace template (Standard, TSQL etc) can be used to capture all the queries in a server/db?
Post #946172
Posted Thursday, July 1, 2010 8:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 908, Visits: 2,584
The Standard Template should get you what you want. Although it is better practice to use a server-side trace for performance purposes.

Which is pretty easy to setup. Quick step is to:
1) Open Profiler
2) Select Standard Template
3) Click Run
4) Immediately Click Stop
5) File > Export > Script Trace Definition > SQL Server 2005...

Save the file and then you can open it up and take out the eventClass(s) that you don't care about. Check here for a list of those: http://msdn.microsoft.com/en-us/library/ms175481(SQL.90).aspx

This is a good SQL Tip to creating the trace as well: http://www.mssqltips.com/tip.asp?tip=1035


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #946191
Posted Thursday, July 1, 2010 11:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
If you would like to capture all the actions on the instance refer "C2 Audit Mode Option".

HTH


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #946590
Posted Friday, July 2, 2010 6:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
I had used the std template(with hostname included). When i ran sp_databases, the trace shows it as two records; one record with duration, starttime, endtime,Reads & CPU and another record has NULL for the same. Any idea to interpret it?
Post #946784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse