Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
m.harte
Starting Member
1 Post |
 Posted - 2004-11-05 : 08:06:28
|
 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. BEGIN DECLARE @objPCPlus intDECLARE @Result int--Create the object using the in built stored procedureEXEC sp_OACreate 'AFDUtilX.Utility', @objPCPlus OUT,4--set the Postcode property of the objectEXEC sp_OASetProperty @objPCPlus, 'Postcode', @strPostcode--Initiate the CheckPostcode routineEXEC sp_OAMethod @objPCPlus, 'CheckPostcode'EXEC sp_OAGetProperty @objPCPlus, 'Result', @Result OUTEXEC sp_OADestroy @objPCPlus OUTReturn @ResultENDDue 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? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-05 : 08:14:29
|
| You can run DBCC FREEPROCCACHE to clear the procedure cache, it may help. The problem is the layering of the calls, a sproc calling an xproc calling a COM. You can call COM objects directly from SQL Server using the sp_OA system procedures (sp_OACreate, sp_OAGetProperty, etc.) See Books Online for more details. This should reduce the overall memory used, and also because destroying a COM object has more likelihood of invoking the garbage collection facility. May even perform faster. |
 |
|
|
|
|
|
|
|
Memory problem….