title: What are the consequences of not deallocating memory from a sql cursor on sql server 2000?
body: <p>I would truly appreciate anyone who could point me toward some resources or share some personal experience on this.  </p>
  
  <p>I noticed that my sql 2k box cpu was pegged at near 100%, it spiked at almost exactly the same time that an etl process which used a sql cursor but did not deallocate memory failed.  I stopped and started the service, and it fixed the problem.</p>
  
  <p>This etl process failed earlier in the morning and when the server spiked the developer was rerunning the process manually.</p>
  
  <p>So in conclusion: my question is this: it seems very obvious that the etl process may have caused the server to spike, however, i would like to know if/how/why this procedure which did not deallocate memory could cause this problem.  The query looked similar to this:</p>
  
  <pre><code>USE AdventureWorks
  GO
  DECLARE @ProductID INT
  DECLARE @getProductID CURSOR
  SET @getProductID = CURSOR FOR
  SELECT ProductID
  FROM Production.Product
  OPEN @getProductID
  FETCH NEXT
  FROM @getProductID INTO @ProductID
  WHILE @@FETCH_STATUS = 0
  BEGIN
  Insert into sometable values(@ProductID)
  FETCH NEXT
  FROM @getProductID INTO @ProductID
  END
  CLOSE @getProductID
  GO
  </code></pre>
  
  <p>Thank you very much.</p>