Pages

SQL Server Release Memory

Feb 24, 2016
In my case, when server run my data transfer task, the server memory will get hight. I need to restart service.
Another way in discuss : [SQL Server Bulk Insert Physical Memory Issue]
I write it to a stored procedure, like bellow:
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[SQL_Server_Release_Memory]    Script Date: 2/24/2016 12:42:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------- 
description: SQL Server Release Memory
author: Robin
date: 2016/02/24
testing code: 
-----------------------------------------------------
EXEC SQL_Server_Release_Memory
-----------------------------------------------------*/
CREATE PROCEDURE [dbo].[SQL_Server_Release_Memory]

AS
BEGIN
/*Query physical memory in use*/
SELECT [description], value_in_use
 , (select top 1 physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) as physical_memory_in_use_mb
FROM sys.configurations
WHERE name ='max server memory (MB)';


DECLARE @MaxMemorySize int;

SELECT @MaxMemorySize = convert(int, value)
FROM sys.configurations
WHERE name ='max server memory (MB)';


/*1.Set Maximum server memory to 1.5GB */
DECLARE @TempMemorySize int
SET @TempMemorySize = 1024 * 1.5;

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'max server memory (MB)', @TempMemorySize RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE;

DECLARE @WaitAndTry int = 0
WHILE (@WaitAndTry < 5)
BEGIN
 /*Check has already released ? */
 WAITFOR DELAY '00:00:10'; 

 IF EXISTS(
  SELECT (physical_memory_in_use_kb/1024)
  FROM sys.dm_os_process_memory
  WHERE (physical_memory_in_use_kb/1024)<2000
 )
 BEGIN
  /*2.Set Maximum server memory to original setting */
  EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'max server memory (MB)', @MaxMemorySize RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE;
  
  SET @WaitAndTry = 10;

  PRINT N'Finish.';
 END
 ELSE
 BEGIN
  SET @WaitAndTry = @WaitAndTry + 1;
  IF @WaitAndTry = 5
  BEGIN
   PRINT N'Fail: have to check and setting [Maximum server memory].';
  END
 END

END

/*Query physical memory in use*/
SELECT [description], value_in_use
 , (select top 1 physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) as physical_memory_in_use_mb
FROM sys.configurations
WHERE name ='max server memory (MB)';

/*
reference 
http://serverfault.com/questions/251832/sql-server-bulk-insert-physical-memory-issue
http://serverfault.com/questions/408919/how-do-you-get-the-minimum-and-maximum-memory-allocation-of-a-sql-instance-using
*/
END
When your server memory get hight,  run this stored procedure will release SQL Server memory .