Microsoft KB Archive/294976

= FIX: SQL Profiler Causes Handled Access Violation in SQL Server While Tracing Large Data =

Article ID: 294976

Article Last Modified on 10/9/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q294976



BUG #: 236428 (SHILOH)



SYMPTOMS
The SQL Profiler application causes a thread-level handled access violation (AV) in SQL Server while tracing events that have large amounts of data (nearing 1 megabytes [MB] in size).

The AV recorded in the error log for the retail SQL Server 2000 version 8.00.194 is: Short Stack Dump 00409216 Module(sqlservr+00009216) (CVariableInfo::PbGetBlock(unsigned long,unsigned long)+0000002A) 00409328 Module(sqlservr+00009328) (CVarPageMgr::PbAllocate(unsigned long)+00000067) 00402062 Module(sqlservr+00002062) (CMemObj::Alloc(unsigned long)+00000020) 00401F5C Module(sqlservr+00001F5C) (CMemProc::Alloc(unsigned long,char *,int,unsigned char)+0000000F) 00401F94 Module(sqlservr+00001F94) (CMemThread::Alloc(unsigned long,char *,int,unsigned char)+0000003C) 0066AC00 Module(sqlservr+0026AC00) (CRowsetTraceData::FGetNextRow(void)+00000127) 0091FD06 Module(sqlservr+0051FD06) (CUtRowset::GetNextRows(unsigned long,long,long,unsigned long *,unsigned long * *)+00000050) 0070FAA3 Module(sqlservr+0030FAA3) (CQScanRmtScan::GetRow(unsigned long *,unsigned long *)+00000155) 00427985 Module(sqlservr+00027985) (CQueryScan::GetRow(unsigned long *,unsigned long *)+00000014) 004271BA Module(sqlservr+000271BA) (CStmtQuery::ErsqExecuteQuery(class CMsqlExecContext *,class CEsComp const *,class CEsComp const *,unsigned long *,int,int)const +0000040D) 00441A34 Module(sqlservr+00041A34) (CStmtSelect::XretExecute(class CMsqlExecContext *)const +0000021C) 004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E) 00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7) 00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343) 0049CD0A Module(sqlservr+0009CD0A) (CStmtExec::XretLocalExec(class CMsqlExecContext *,unsigned short *,int,class WParseName *)const +00000154) 0049CB9F Module(sqlservr+0009CB9F) (CStmtExec::XretExecute(class CMsqlExecContext *)const +00000322) 004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E) 00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7) 00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343) 00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8) 004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0) 410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264) 4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC) 7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE) 77E837CD Module(KERNEL32+000037CD) (TlsSetValue+000000F0) The Input buffer recorded in the error log shows this:

exec sp_trace_getdata 1, 0.



CAUSE
The event produced was nearing 1 MB in size. SQL Profiler uses 16 blocks of memory, each 64 kilobytes (KB) in size, in which to place events. As the size of the event approaches 1 MB, the event takes up all the memory in all of those blocks and results in an overflow, causing the AV.

This error message occurs if the event exceeds 1 MB in size:

Some trace events have not been reported to SQL Profiler because the server has reached its maximum amount of available memory for the process.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix
NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 1.

The English version of this fix should have the following file attributes or later:   File name ---

s80210i.exe NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.



WORKAROUND
If possible, do not use SQL Profiler to trace large events like Data Transformation Services (DTS) tasks, large Transact-SQL batches, and so forth.



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

Keywords: kbbug kbfix kbqfe kbsqlserv2000sp1fix KB294976

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.