May 19, 2011 at 3:55 am
Trying to run a server side trace on one of our production boxes. The trace stops running immediately after I execute it, I cannot see it when I select from fn_trace_getinfo...
The trace does create a 7\10 kb trace file (@MaxFileSize=1000), when I try to select from this file I get the following error "Encountered an error or an unexpected end of trace file '********'.
Also when I check the error log I can see the following error:
2011-05-19 11:34:21.34 spid188 SQL Trace ID 2 was started by login "my windows account".
2011-05-19 11:34:23.15 spid275 Error: 19099, Severity: 16, State: 1.
2011-05-19 11:34:23.15 spid275 Trace ID '2' was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem.
2011-05-19 11:34:23.16 spid275 SQL Trace stopped. Trace ID = '2'. Login Name = 'guest'.
When I run the exact same trace on a different server it runs perfectly fine. I don't know what to do anymore... Hope someone can help. I've tried saving the trace results to different locations (all having plenty of space available) and nothing seems to work.
Box is running SQL 2005 sp4 64 bit
Trace Definition File:
DECLARE @MaxFileSize BIGINT
DECLARE @EndTime DATETIME
DECLARE @OutputFileName NVARCHAR(256)
SET @MaxFileSize = 100
SET @OutputFileName = 'z:\' +
CONVERT(VARCHAR(20), GETDATE(),112) +
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')
SET @EndTime = DATEADD(mi,30,getdate())
exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 97, 9, @on
exec sp_trace_setevent @TraceID, 97, 2, @on
exec sp_trace_setevent @TraceID, 97, 10, @on
exec sp_trace_setevent @TraceID, 97, 14, @on
exec sp_trace_setevent @TraceID, 97, 11, @on
exec sp_trace_setevent @TraceID, 97, 35, @on
exec sp_trace_setevent @TraceID, 97, 12, @on
exec sp_trace_setevent @TraceID, 96, 9, @on
exec sp_trace_setevent @TraceID, 96, 2, @on
exec sp_trace_setevent @TraceID, 96, 10, @on
exec sp_trace_setevent @TraceID, 96, 14, @on
exec sp_trace_setevent @TraceID, 96, 11, @on
exec sp_trace_setevent @TraceID, 96, 35, @on
exec sp_trace_setevent @TraceID, 96, 12, @on
exec sp_trace_setevent @TraceID, 68, 1, @on
exec sp_trace_setevent @TraceID, 68, 9, @on
exec sp_trace_setevent @TraceID, 68, 2, @on
exec sp_trace_setevent @TraceID, 68, 6, @on
exec sp_trace_setevent @TraceID, 68, 10, @on
exec sp_trace_setevent @TraceID, 68, 14, @on
exec sp_trace_setevent @TraceID, 68, 11, @on
exec sp_trace_setevent @TraceID, 68, 35, @on
exec sp_trace_setevent @TraceID, 68, 12, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 71, 8, @on
exec sp_trace_setevent @TraceID, 71, 12, @on
exec sp_trace_setevent @TraceID, 71, 9, @on
exec sp_trace_setevent @TraceID, 71, 10, @on
exec sp_trace_setevent @TraceID, 71, 14, @on
exec sp_trace_setevent @TraceID, 71, 3, @on
exec sp_trace_setevent @TraceID, 71, 11, @on
exec sp_trace_setevent @TraceID, 71, 35, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 41, 15, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 18, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 35, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 40, 8, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 9, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 3, @on
exec sp_trace_setevent @TraceID, 40, 11, @on
exec sp_trace_setevent @TraceID, 40, 35, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 73, 8, @on
exec sp_trace_setevent @TraceID, 73, 12, @on
exec sp_trace_setevent @TraceID, 73, 9, @on
exec sp_trace_setevent @TraceID, 73, 10, @on
exec sp_trace_setevent @TraceID, 73, 14, @on
exec sp_trace_setevent @TraceID, 73, 3, @on
exec sp_trace_setevent @TraceID, 73, 11, @on
exec sp_trace_setevent @TraceID, 73, 35, @on
exec sp_trace_setstatus @TraceID, 1
May 19, 2011 at 5:33 am
One thing you don't have is rollover on the files enabled:
exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime
That should get changed to this:
exec @rc = sp_trace_create @TraceID output, 2, @OutputFileName, @MaxFileSize, @EndTime
That's the one thing that pops out for me. If you change the options so that it rolls over I think you'll see the behavior change, but I could be wrong.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 19, 2011 at 5:57 am
I tried that, it then just generates a series of 14kb trace files...
May 19, 2011 at 5:59 am
Is the hd full?
Sorry, missed that line in the op.
May 19, 2011 at 6:00 am
Nope
May 19, 2011 at 6:22 am
Jeez, ok never mind, found the problem...
Someone set hyperbac up to compress trace files, thats why i could read them and why they didnt grow to the required size.
Thanks for the replies.
May 19, 2011 at 6:25 am
:w00t: hadn't see that one b4 !
May 19, 2011 at 6:49 am
I didn't even know Hyperbac could be set to compress trace files. Cool!
By the way, rollover is supposed to give you a series of files, so that was working as expected.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 4, 2011 at 8:11 am
Hi Guys.
"Encountered an error or an unexpected end of trace file" allso happens (asometimes) if you store the tracefile on a 4K sector size formatted disk.
Move the file to a traditional 512 Byte drive...
Problem solved. 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply