.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Profiler trace 'automatically' reset

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

I'm running a trace based on the SP_Counts template which will help me track all active sprocs in a given database.  My intent was to run the trace 24/7 over the course of several weeks to capture as many sprocs as possible.

Here's my setup:

  -- Server 2:  Profiler is running the trace and collecting sproc usage data from Server 1.

  -- Server 2:  A maintenance database houses the table into which the trace results are saved.

  -- Server 2:  Hourly, a job is run to aggregate the sproc data from the trace table into a different table; the profiler table is then truncated.

What I didn't plan or ask for - and the point of this question - is the trace being reset at around 2:30a every morning.  When I look at the default trace from Server 1, I see the sproc sp_trace_setstatus was called with the TraceID 2 (there are only 2 traces running on my production server; 1 is the default; 2 is my sproc collection trace) and a status of '2' (close the trace and delete trace info) at the time of the reset. 

I've looked over all the jobs I've created between the two servers and there's nothing on either of them running at the time of the reset.  Since I'm dumping the results to a table (and did not limit the number of rows) I don't think the 'max size' property of the trace would matte

View Complete Post

More Related Resource Links

adding filter to sqldiag profiler trace

Hi everyone I'm wanting to create some standard diagnostics scripts for SQL Server 2005 installations and sqldiag seems the most logical way to do this. However, I want to be able to add filters to ProfilerCollector so that I can capture data for a specific database, application, etc. I've searched but can not find any information on either adding filters - which I suspect that you can not do - or specifying filters by using the template attribute.  However, Profiler itself does not seem to export templates as .xml which is the format of the default file specified for the template attribute! Does anyone know if adding filters is possible, by whatever mechanism, and if so does anyone have any examples?  It would be very much appreciated. thanks, and regards Simon

Trace My stored procedure Without using profiler



I am working on some application. We are using Sqlserver2008. We dont have Sql profiler to trace stored procedure and all. We can't install also imidiately.

can anyone give some idea how to trace which stored procedure is hiting my Sql server.

we are using asp.net 2.0 and sqlserver2008.

Is there any same kind offeatures avl in VS 2005?  

plz suggest.



SQL Profiler & Trace flag vs Performance


Hi all,

I just wonder how much the profiler will affect the live server's performance if I capture the following event for deadlock graph.
Locks -> Deadlock Graph

Also, if I onlyopen the trace, i.e. open the 1204 and 1022 trace flag, capture deadlock info in SQL Errorlog rather than capture the deadlock graph in Profiler, will it also affect the performance, and how much it will affect?

----> Kinect

Displaying Execution Plans by Using SQL Server Profiler Event Classes - Filtering the trace?


IN SQL Profiler you have the ability to see the execution plan for each query, which sometimes can be helpful. However, I'm not able to make use of any of my filters when profiling with the "Showplan XML" event class enabled.

Say, I want to sample only batches with a duration > 100 ms, how can I make this filter take effect? I have no problems with applying such filters when _not_ using these event classes.

I'm using SQL Server 2008.


Henrik F

Is there a way to configure a new SQL Server Profiler trace to be named same as name of the template

After I created a template and then use the template for a trace, most of the time I want the trace to be named same as or very similar to the template.   If SQL Server Profiler allows to copy the trace name, I don't mind to copy/paste but it does not.

Automatically redirect to HTTPS and check if SSL is active



I've set in the web.config:

    <!-- User Authentication -->
    <authentication mode="Forms">
      <forms loginUrl="~/Page/View/Content/Login.aspx" timeout="2880" requireSSL="true" />

This works fine. If there is no ssl connection, the login form doesn't continue. But I would like to check if ssl is active (in codebehind) and, if it's possible, to automatically the user to the https page if he's currently on http. (I could do this job by manually redirect with page.response("https://url.../login.aspx"); but this is not really generic. For example if the webapp is moved to another domain it doesn't work anymore... I'm looking for something like: FormsAuthentication.RedirectWithSSL(); or so... :D)


Nested Tracers - is there a way to only log the outer trace?



I have some nested tracers. The outer one has a category of 'PageTrace' and the inner ones have categories of 'DBTrace'. When I set the outer category source to 'All' I get both traces. When I switch on the inner trace, I just get the inner trace.

So, my question: is there any way to just log the outer trace events, i.e. the Page Traces.




Performance: Find Application Bottlenecks with Visual Studio Profiler


We will introduce you to the Visual Studio Profiler by walking through a sample performance investigation, pinpointing code inefficiencies in some sample applications.

Hari Pulapaka and Boris Vidolov

MSDN Magazine March 2008

Extreme ASP.NET: Tools of the Trade: SQL Server Profiler and Query Analyzer


In my last column, I discussed Microsoft® Application Center Test and how it could be used to measure the performance of your Web application (see Extreme ASP. NET: Tools of the Trade: Application Center Test).

Rob Howard

MSDN Magazine August 2005

CLR Profiler: No Code Can Hide from the Profiling API in the .NET Framework 2.0


The common language runtime (CLR) profiling API makes available information about the application domains, assemblies, and classes that are loaded and used in a process, just-in-time (JIT) compiler notifications, memory usage tracking, tracing of events, exception tracking, managed to unmanaged code transitions, and the state of the runtime. And if that weren't enough, you will find a nicely enhanced profiling API in the .NET Framework 2.0. Find out what's coming up in this next version.

Jay Hilyard

MSDN Magazine January 2005

Design: Inspect and Optimize Your Program's Memory Usage with the .NET Profiler API


Developers using .NET often make memory leak tracking a low priority because the common language runtime takes care of garbage collection. What few developers realize, however, is that their objects' lifespans, along with their size and what other objects have been instantiated, all affect how they are cleaned up. Depending on the particular circumstances, these combinations can negatively affect performance, especially over the lifetime of an application. This article presents a way for developers to see memory usage and understand garbage collection using the .NET Profiler API. Along the way, a sample application to demonstrate these principles is built.

Jay Hilyard

MSDN Magazine January 2003

Autoplay in Windows XP: Automatically Detect and React to New Devices on a System


The last few years have seen dramatic growth in digital multimedia content and an increasing number of devices generating such content. Accordingly, the Autoplay feature of Microsoft Windows has been enhanced to handle these scenarios. This article presents samples that use the new features and provides advice on how to take advantage of these additions. The topics covered include Autoplay support for audio CDs and for DVD movies, the Autoplay registration process, event handlers, and how your software can cancel Autoplay when you don't want certain files interrupted. The download includes tools for diagnosing problems and to help the reader understand the shell hardware detection service processing of hardware events.

Stephane St-Michel and Brian Aust

MSDN Magazine November 2001

Automatically Execute Code at certain time from website


Is there a way to trigger a piece of code to execute at a certain time once a week?

I basically want my website to send out a reminder email every sunday. I have a function called SendReminder() and I would like to know if it is posible for it to run by itself because at the minute I have to press a button to run it.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend