FIX: Memory Leak May Occur When Calling Bulk Insert From a Stored Procedure

This article was previously published under Q246824
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55303 (SQLBUG_70)
SYMPTOMS
A memory leak in Sqlservr.exe occurs with each execution of a BULK INSERT that is called from a stored procedure. If the stored procedure is run by a recurring task, it is possible to experience access violations or other errors.
WORKAROUND
Use the bcp utility instead of BULK INSERT.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
MORE INFORMATION
You can use the following stored procedure to reproduce the problem:
create proc p_monitor_BcpLoad --(@servername varchar(20))as  declare @statctr int  declare @snapctr int  declare @msg varchar(255)--   declare @ctr int  declare @cmd varchar(255)  begin tran--  delete pubs..event where MachineName = 'Test'  select @cmd = "Bulk Insert PUBS..event from 'c:\customer\don\vevent.dat'" --		  exec (@cmd)  select @statctr = @@rowcount  select @msg = 'event loaded= ' + convert(varchar(5),@statctr)  print @msg  print 'Update stat Zeros'  update PUBS..event      set status = 1 where MachineName = 'Test' --and control_date is null--  select @cmd = "Bulk Insert PUBS..logdetl from 'c:\customer\don\logdetl.dat'"   exec (@cmd)  select @snapctr = @@rowcount  select @msg = 'logdetl loaded= ' + convert(varchar(5),@snapctr)  print @msg--  delete pubs..event2 where MachineName = 'Test'  select @cmd = "Bulk Insert PUBS..event2 from 'c:\customer\don\vevent.dat'"  exec (@cmd)  update PUBS..event2      set status = 1 where MachineName = 'Test'  if @@rowcount <> 101     BEGIN       raiserror 50001 'Row missing in event2 Table - Bcp failed '     END      commit tran  return				
The text of the access violation (av) for this problem is:
1999-04-12 15:55:03.85 spid13   Using 'sqlimage.dll' version '4.0.5'Stack Dump being sent to C:\MSSQL7\log\SQL00001.dmp1999-04-12 15:55:29.45 spid13   Error: 0, Severity: 19, State: 01999-04-12 15:55:29.45 spid13   SqlDumpExceptionHandler: Process 13generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Serveris terminating this process..**************************************************************************** BEGIN STACK DUMP:Short Stack Dump0x00507a5b Module(sqlservr+107a5b) (build_sort_collist+f6)0x0050799b Module(sqlservr+10799b) (RowsetSorted::Init+c6)0x00507855 Module(sqlservr+107855) (CreateRowsetSort+50)0x00507649 Module(sqlservr+107649) (CQScanSort::CreateSortTable+6a)0x00507586 Module(sqlservr+107586) (CQScanSort::CQScanSort+89)0x005074f0 Module(sqlservr+1074f0) (CXteSort::QScanGet+38)0x00522d3f Module(sqlservr+122d3f) (CQScanTop::CQScanTop+77)0x00522cbb Module(sqlservr+122cbb) (CXteTop::QScanGet+38)0x0041e216 Module(sqlservr+1e216) (CQueryScan::CQueryScan+249)0x0041e08a Module(sqlservr+1e08a) (CQuery::Execute+4f)0x004113c9 Module(sqlservr+113c9) (CStmtQuery::FExecuteQuery+216)0x00416c57 Module(sqlservr+16c57) (CStmtSelect::XretExecute+19c)0x004127b8 Module(sqlservr+127b8) (CMsqlExecContext::ExecuteStmts+118)0x00412279 Module(sqlservr+12279) (CMsqlExecContext::Execute+16b)---------------------------------------------------------------------------1999-04-12 15:55:38.69 spid13   Error: 0, Severity: 19, State: 01999-04-12 15:55:38.69 spid13   language_exec: Process 13 generated anaccess violation. SQL Server is terminating this process..1999-04-12 15:56:02.93 spid13   Error: 1509, Severity: 20, State: 41999-04-12 15:56:02.93 spid13   Row compare failure..1999-04-12 15:57:04.13 spid13   Error: 1509, Severity: 20, State: 41999-04-12 15:57:04.13 spid13   Row compare failure..1999-04-12 15:58:01.65 spid13   Error: 1509, Severity: 20, State: 41999-04-12 15:58:01.65 spid13   Row compare failure..1999-04-12 15:59:01.72 spid13   Error: 1509, Severity: 20, State: 41999-04-12 15:59:01.72 spid13   Row compare failure......				
Properties

Article ID: 246824 - Last Review: 10/22/2013 03:02:07 - Revision: 3.1

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix KB246824
Feedback