Is it good to run profiler continuously? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is it good to run profiler continuously?

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-18 : 07:21:32

Dear Experts,
my production box is APDB which runs windows server 2003 with sql server 2005.
i'd like to run the profiler on a client machine for production. will it affect the production? is it good to take the traces and put those as input to the database tuning advisor?

if it is not a problem running the profiler continuously, please provide me some good links to support this.
please help me in this regard

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-18 : 07:31:25

You can run profiler continually. It's going to hurt the server's performance, but you can do it.

I've just finished with a thread where profiler brought the server down
http://www.sqlservercentral.com/Forums/Topic620889-360-1.aspx

If you want a permanent trace, use a server-side trace rather. (look up the sp_trace* procedures in Books Online). You can get profiler to create the script for you.

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-18 : 09:28:07

Any reason to run Profiler continously?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-18 : 11:38:46

quote:
Originally posted by sodeep

Any reason to run Profiler continously?

Security audit's one I've run into before. Can't think of any other good reason.

--
Gail Shaw
SQL Server MVP

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-18 : 13:51:30

In SQL Server 2005 there is a default trace that does run continuously.

It does not gather a huge number of events, but it is there and running by default.

Auditing: SQL Server’s Built-in Traces
http://msdn.microsoft.com/en-us/library/cc297240.aspx

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-18 : 13:55:50

We run a server-side trace continuously on a mission critical application. We can not run SQL Profiler continuously on this system as we would seriously impact performance. To reduce the amount of resources that SQL Profiler uses, see this: http://weblogs.sqlteam.com/tarad/archive/2008/08/06/SQL-Profiler-best-practices.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-19 : 01:36:57

MVJ, thank you for the good link...
but i'm not sure how can i trace? for example, a user dropped a table. hoe can find by using the trace? i missed at some where in the article. please guide me

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-19 : 02:33:26

Depends on what events you're tracing. If you have the batch completed, search through the trace output for the Drop Table statement. If you're talking about the default trace, look for the Object:Deleted event

Google has lots and lots of good links on the default trace
http://blogs.technet.com/beatrice/archive/2008/04/29/sql-server-default-trace.aspx
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
http://blogs.technet.com/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

btw, your users should not have enough rights to drop a table. If they do, that's a serious stuff up.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources