CODEWITHSUNDEEP
sql-server-2008-r2
zahra moeini
zahra moeini

Reputation: 3

How can I release RAM that is used by the database after database work finished?

How can I release RAM that is used by the database after database work finished? (the RAM usage is 1.57 before running SP and after that, SP running and finished, RAM usage is 2.24 - when restart SQL Server it's will be OK).

We have just one multi-purpose server. This server is SQL Server Host, and we also run other enterprise applications on this server, and these applications need free RAM space too.

How can I ensure these applications have enough RAM to run?

Upvotes: 0

Views: 175

Answers (2)

Sachin Tanwar - .Net Developer
Sachin Tanwar - .Net Developer

Reputation: 347

Go for a change in the 'max server memory' setting. If you need more help, read the post Used memory not freed up after a SQL BULK insert / BCP export.

Upvotes: 1

Masoud Sahabi
Masoud Sahabi

Reputation: 223

I had such a problem before, and I found a solution for this issue. You can create a stored procedure as described below: Before you start your application, you should call this stored procedure in Mode 1, because you need sufficient memory.

For SQL operations and before closing your application, you have to call this stored procedure again in Mode 0:

Create Proc  [dbo].[MP_Rpt_ConfigureMemory]
(  @Mode  bit  )
as
declare @RAM as integer
declare @MAX as integer
declare @MIN as integer

set @RAM =
(SELECT

      [physical_memory_in_bytes]/1048576 AS [RAM (MB)]

FROM  [sys].[dm_os_sys_info] )

Set @MAX = ((@RAM / 4) * 3)
Set @MIN = ((@RAM / 4) * 1)

if @Mode=0
begin
      exec SP_Configure 'min server memory', 1
      RECONFIGURE
      exec SP_Configure 'max server memory', 100
      RECONFIGURE
end
else
if @Mode = 1
begin
      exec SP_Configure 'max server memory', @MAX
      RECONFIGURE
      exec SP_Configure 'min server memory', @MIN
      RECONFIGURE
end

Upvotes: 0

Related Questions