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

View products that this article applies to.

This article was previously published under Q246824

BUG #: 55303 (SQLBUG_70)

Expand all | Collapse all

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.

Back to the top

WORKAROUND

Use the bcp utility instead of BULK INSERT.

Back to the top

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 (http://support.microsoft.com/kb/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.

Back to the top

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.dmp
1999-04-12 15:55:29.45 spid13   Error: 0, Severity: 19, State: 0
1999-04-12 15:55:29.45 spid13   SqlDumpExceptionHandler: Process 13
generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
.
***************************************************************************
* BEGIN STACK DUMP:

Short Stack Dump
0x00507a5b 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: 0
1999-04-12 15:55:38.69 spid13   language_exec: Process 13 generated an
access violation. SQL Server is terminating this process.
.
1999-04-12 15:56:02.93 spid13   Error: 1509, Severity: 20, State: 4
1999-04-12 15:56:02.93 spid13   Row compare failure..
1999-04-12 15:57:04.13 spid13   Error: 1509, Severity: 20, State: 4
1999-04-12 15:57:04.13 spid13   Row compare failure..
1999-04-12 15:58:01.65 spid13   Error: 1509, Severity: 20, State: 4
1999-04-12 15:58:01.65 spid13   Row compare failure..
1999-04-12 15:59:01.72 spid13   Error: 1509, Severity: 20, State: 4
1999-04-12 15:59:01.72 spid13   Row compare failure..
....

Back to the top