SQL 2005 (Trace Template to capture all queries)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL 2005 (Trace Template to capture all...

SQL 2005 (Trace Template to capture all queries)

Rate Topic

Display Mode

Topic Options

Author

Message

balasach82

Posted Thursday, July 01, 2010 7:31 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

What trace template (Standard, TSQL etc) can be used to capture all the queries in a server/db?

Post #946172

Shawn Melton

Posted Thursday, July 01, 2010 8:01 AM

Right there with Babe

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:56 PM
Points: 754, Visits: 1,891

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
@wshawnmelton

Post #946191

free_mascot

Posted Thursday, July 01, 2010 11:34 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:39 AM
Points: 1,768, Visits: 1,312

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

balasach82

Posted Friday, July 02, 2010 6:56 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 8:55 AM
Points: 307, Visits: 743

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 »

Permissions