August 13, 2012 at 8:37 am
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!
August 13, 2012 at 8:44 am
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
August 13, 2012 at 9:15 am
a Custom trace.
August 14, 2012 at 7:34 am
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
August 14, 2012 at 1:25 pm
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!
August 16, 2012 at 6:29 am
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