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?