SQL Server Release Memory
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:
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 */ ENDWhen your server memory get high, run this stored procedure will release SQL Server memory .