What are the consequences of not deallocating memory from a sql cursor on sql server 2000?

I would truly appreciate anyone who could point me toward some resources or share some personal experience on this.

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.

This etl process failed earlier in the morning and when the server spiked the developer was rerunning the process manually.

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 cau开发者_开发知识库se this problem. The query looked similar to this:

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

Thank you very much.

---

Yikes. You have a nested cursor, which could create a very large number of resources allocated. I won't say that's definitely your problem, but it is definitely a problem.