Post reply

Method for triggering the automatic start of a SQL Trace

  • June 1, 2015 at 7:22 am

    #304550

    Hi

    I support a WebApp which uses SQL Server 2005, with thousands of users reading/writing to a database. There are sporadic periods when most users are reporting 'Timeout Expired' errors when using the WebApp. We believe this is down to a user(s) running large queries which is blocking other SQL statements waiting to process. We are aiming to find which users/queries are causing these problem periods.

    We have a SQL Trace set up & running on a test system, however the trace is manually started/stopped via 2 separate SQL jobs. We don't want to have the trace running daily/indefinitely to capture data for a problem which is sporadic.

    Is there a way to configure SQL, to automatically start a trace should the above described problem arise i.e. should CPU usage go above a certain level or SQL detect a blocked process, or via some other trigger etc?

    Any advice appreciated.

    Thanks

    Mick

  • June 1, 2015 at 8:22 am

    #1802005

    you can create a different job that monitors things, and have that job run every minute or something;

    if it's critera is matched, , and the tace is not already started, then you can call msdb.dbo.sp_start_job job_name='My Job That Starts The Trace'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 2, 2015 at 5:02 am

    #1802241

    Thanks Lowell - will give this a go

  • June 3, 2015 at 4:35 am

    #1802533

    If you script out your trace you can also have Windows Data Collector Sets setup an alert that can trigger the trace if certain counter limits are exceeded. I have done that in the past to kick off traces that only last for about 5-15 minutes but only get kicked off when CPU usage is above 95%.

    Joie Andrew
    "Since 1982"

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply