sql traces capturing all the sp_reset_connections all the time

  • I am wondering what is going on with the trace?

    It is a production sql 2005 box Enterprise Edition 2005.

    The schedule is Monday to Friday from 8am to 6pm (hourly).

    Only the 8am trace is very very big like over 2 GB in size and the others (9am to 6pm traces, each one are much smaller like 200 MB). That means it runs from 8am to 8:59am. Till it wait for the next schedule at 9am, then it will run from 9am to 9:59am, etc...

    When I check the 8am trace, it is showing the majoriy of the textdata is:

    sp_reset_connection.

    If it is a Monday Trace at 8am (8/13), the starttime will show from Sunday night (1am, 2am, etc, ... all the way to Tuesday 8:59am).

    The duration is 0.

    The tracefile has a modified date of 8/12 1:41am.

    Why would the trace shows a sp_reset_connection from the previous date? Shouldn't it only shows from 8am onward?

    It baffles me.

    Anyone know is there any logical explanation? If you do solve this problem, you are a genius!

  • Are you talking about the default trace, or a custom trace?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • a Custom trace.

  • Hollyz (8/13/2012)


    a Custom trace.

    At the very least, we'd need to know what the trace definition is. What events is it capturing, and what characteristics of them?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The following columns it is capturing are:

    TextData

    BinaryData

    DatabaseID

    TransactionID

    LineNumber

    NTUserName

    NTDomainName

    HostName

    ClientProcessID

    ApplicationName

    LoginName

    SPID

    Duration

    StartTime

    EndTime

    Reads

    Writes

    CPU

    Permissions

    Severity

    EventSubClass

    ObjectID

    Success

    IndexID

    IntegerData

    ServerName

    EventClass

    ObjectType

    NestLevel

    State

    Error

    Mode

    Handle

    ObjectName

    DatabaseName

    FileName

    OwnerName

    RoleName

    TargetUserName

    DBUserName

    LoginSid

    TargetLoginName

    TargetLoginSid

    ColumnPermissions

    LinkedServerName

    ProviderName

    MethodName

    RowCounts

    RequestID

    XactSequence

    EventSequence

    BigintData1

    BigintData2

    GUID

    IntegerData2

    ObjectID2

    Type

    OwnerID

    ParentName

    IsSystem

    Offset

    SourceDatabaseID

    SqlHandle

    SessionLoginName

    PlanHandle

    The following events that is captured on the trace is:

    53) cursor open. how often used ?

    79) missing column stats. Is this OK to have ?

    80) Missing join predicate. could be long-running

    25) Lock:Deadlock

    59) Lock:DeadlockChain

    18) ServiceControl

    46) Object:Created [strategy: temp tables]

    47) Object:Deleted [strategy: temp tables]

    14) Login **IMPORTANT**

    15) Logout **IMPORTANT**

    17) ExistingConnection **IMPORTANT**

    20) LoginFailed

    92) DataFileAutoGrow

    93) LogFileAutoGrow

    10) RPC:Completed *********PERFORMANCE DATA****

    12) SQL:BatchComppleted ***PERFORMANCE DATA****

    16) Attention (broken connection?)

    21) EventLog (NT app log, includes SQL error log)

    22) ErrorLog (SQL)

    26) Lock:Cancel

    27) Lock:Timeout

    33) Exception

    61) OLE DB Errors

    69) Sort Warnings (cannot fit in memory, using tempdb?)

    81) Server Memory change (by 5% of max)

    92) Data File Auto Grow [impact performance ?]

    93) Log File Auto Grow [impact performance ?]

    94) Data File Auto Shrink

    95) Log File Auto Shrink

    58) AutoUpdate statistics

    28) Degree of Parallelism

    37) Recompile event

    xx)

    41) SQL:StmtCompleted ***WARNING: same as 45 (below) in SQL2000

    45) SP:StmtCompleted ***WARNING: TRACES INSIDE OF SP's!

  • I'm going to hazzard that one or more servers connecting to this database are rebooting, restarting services, or otherwise dropping and rebuilding connections, during the window you're looking at. It's probably as simple as that.

    To really go further on something this complex on your server, I'd have to have access to your servers.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 6 posts - 1 through 5 (of 5 total)

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