The Default Trace in SQL Server 2005

In this video Brian covers the default trace for SQL Server 2005. The default trace is used to capture events on a sql server 2005 instance and is enabled by default. He will show you where to find this trace and view the contents of the trace file.

Brian is a SQL Server author, columnist, and MVP focusing primarily on SQL Server security. He currently serves as database administrator / architect at AgFirst Farm Credit Bank where he can focus on his passion: SQL Server. He formerly served as a systems and security architect for AgFirst Farm Credit Bank where he worked on Active Directory, Windows security, VMware, and Citrix. In the technical...

Venkata Taduri on 2/24/2009

Thank you.

bhaskar on 9/21/2009

Very Useful

josh on 9/21/2009

interesting introduction to a very useful feature. perhaps could use more explanation. Can I add my own report and/or trace?

Janet Josephson on 9/21/2009

Needed to be more basic. I didn't see where he got Profiler from.

peter nicolson on 9/21/2009

talks a bit fast....

Carrie on 9/22/2009

A little confusing. I couldn't find my reports for SQL server. I didn't know if this was an option I could turn on or not. I also was not familiar with the SQL Server Profiler so this wasn't much help either.

mbourgon on 9/22/2009

Would appreciate it if you could add a comment about whether there are any hazards in opening the most recent trace file.

K. Brian Kelley on 9/23/2009

If you have a default install of the SQL Server 2005 client tools installed, you won't see the reports. That was functionality added to Service Pack 2. You'll need to install the latest SQL Server 2005 service pack on the system where you want to see reports (even if it's own client tools) in order to add this functionality to SQL Server Management Studio (SSMS).

K. Brian Kelley on 9/23/2009

SQL Server Profiler is a basic tool that allows you to see activity on your SQL Server. You should find it at Start | All Programs | SQL Server 2005/2008 | Performance Tools | SQL Server Profiler.

K. Brian Kelley on 9/23/2009

There shouldn't be any danger reading the most recent trace file.

Diana Dee on 9/24/2009

Brian, you speak way too fast. Slow down and show more detail. 3 minutes is beetter than 2. Thanks.

aks on 9/26/2009

Good one...

ShaheedulHaq Saad on 12/11/2009

nice tutorial, but need to elaborate certain things like why there were so many .log files?

Steve Harris on 12/22/2009

how would you start the trace or modify it?

Robert Johnson on 1/6/2010

Instructor talks too fast and doesn't clearly explain the facets of the video.

Oleg Goldberg on 4/8/2010

My company firewall configuration does not allow video streaming. Any chances you will make your videos available for download ? Thank you

Feodor Georgiev on 5/23/2011

Here is a good set of SSMS reports which query the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq/

Vijay S on 1/25/2012

Very nice. Thanks

Jim Orten on 1/26/2012

Learned something new. Thanks

b on 1/26/2012

Keep them coming!

Jamshid Nouri on 1/26/2012

excellent!

tim cronin on 1/26/2012

great

faheem on 1/26/2012

More useful and faster alternative: SELECT top 1000 * FROM fn_trace_gettable('trace file path',default) ORDER BY starttime desc

Mick on 1/26/2012

Doesn't seem very practical.

John O'Sullivan on 1/26/2012

very good tip

Steve Harris on 1/26/2012

Maybe add how you change the default trace. Also, what important inforation could you get and use from the trace.

Russell Todd on 1/30/2012

seemed chopped off-just ended mid sentence

172A9B06FE on 1/30/2012

Too brief and too shallow. Some good info but definitely needed more details.

manu jaidka on 2/12/2012

Very useful