Thread: Memory problem….
11-05-04, 11:58 #1Registered User
- Join Date
- Nov 2004
Unanswered: Memory problem….
I’m having a problem with SQL Server fragmenting memory and not being able to obtain a large enough block of contiguous memory for some operations to run.
I have a number of stored procedures which, using UDFs call extended stored procedures, which in turn call COM objects (see UDF code below). Over a period of some weeks use memory seems to become fragmented and the stored procedures will error due to lack of contiguous memory.
DECLARE @objPCPlus int
DECLARE @Result int
--Create the object using the in built stored procedure
EXEC sp_OACreate 'AFDUtilX.Utility', @objPCPlus OUT,4
--set the Postcode property of the object
EXEC sp_OASetProperty @objPCPlus, 'Postcode', @strPostcode
--Initiate the CheckPostcode routine
EXEC sp_OAMethod @objPCPlus, 'CheckPostcode'
EXEC sp_OAGetProperty @objPCPlus, 'Result', @Result OUT
EXEC sp_OADestroy @objPCPlus OUT
Due to the many connections which would have to be re-established and its 24-7 use stopping and starting SQL Server is not an ideal solution.
Is there any way of forcing a memory ‘clear out’ without stopping SQL Server that I can schedule to run at a low demand time?
11-06-04, 02:40 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
Who's 'AFDUtilX.Utility' and is it bug-free in respect to all the calls to it that you mentioned?"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."