MemToLeave /32bit MS SQL Server

32bit MS SQL Server에 할당 되는 MemToLeave 영역의 한계 때문에 대용량 Database 백업 파일의 복구가 실패 되는 현상이 있었습니다.  32bit 시스템의 메모리 아키텍처상  어쩔 수 없었습니다만 몇 일 밤새며 고생한거 생각 하니 ....
흑흑....

SQL서버의 메모리는 크게 Buffer Pool(8KB 페이지로 구성)과 MemToLeave 영역으로 구분되며 MemToLeave 영역은 Linked Server 쿼리, Extended Stored Procedure, COM 모듈 등의 외부 모듈에 의해서 주로 사용되며 Backup/Restore를 위한 Buffer 공간 및 8KB를 초과하는 연속된 공간이 필요할 경우에 SQL서버에 의해서 사용되기도 합니다. 

32bit MS SQL Server 2008에서 기본값은 384M 인 것 같습니다. 그런데 technet site에는 256M로 설명이 되어 있네요.
http://msdn.microsoft.com/ko-kr/library/ms190737(v=SQL.100).aspx

What is MemToLeave?
MemToLeave is virtual address space (VAS) that’s left un-used when SQL Server starts so that external components called by SQL Server are saved some address space. So in order for these technologies, .NET CLR, Linked Servers and extended stored procedures, to operate efficiently you must ensure that they too have access to sufficient memory.

[SQL Server Memory Architecture]

 

[MemToLeave가 부족할 때 나타나는 증상들]

출처: http://optimizer.tistory.com/entry/MemToLeave에서-가장-큰-연속된-공간Max-Free-Block-확인

1. error 17189

오류: 17189, 심각도: 16, 상태: 1
SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [클라이언트: 121.139.219.130]

2. error 18056
오류: 18056, 심각도: 20, 상태: 29
The client was unable to reuse a session with SPID 302, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

3. error 17803
오류: 17803, 심각도: 20, 상태: 5 사용할 수 있는 메모리가 부족합니다..

WARNING:  Failed to reserve contiguous memory of Size= 65536.

4. error 512
Non-interface error:  OLE DB provider SQLOLEDB returned an incorrect value for properties changed which should be for schema checking

Error: 512, Severity: 16, State: 1

5. login 실패
SQL Server에서 process_loginread 스레드를 만들 수 없습니다.

6. 백업 실패

7. 기타 ...

[MemToLeave 영역 사이즈 확인 방법]
아래 쿼리를 실행 하면 해당 서버에 할 당 된 MemToLeave 영역을 확인 할 수 있습니다.

 

With VASummary(Size,Reserved,Free) AS

(SELECT

    Size = VaDump.Size,

    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)

    WHEN 0 THEN 0 ELSE 1 END),

    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)

    WHEN 0 THEN 1 ELSE 0 END)

FROM

(

    SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))

    AS Size, region_allocation_base_address AS Base

    FROM sys.dm_os_virtual_address_dump 

    WHERE region_allocation_base_address <> 0x0

    GROUP BY region_allocation_base_address 

 UNION  

    SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address

    FROM sys.dm_os_virtual_address_dump

    WHERE region_allocation_base_address  = 0x0

)

AS VaDump

GROUP BY Size)

 

 

SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB] 

FROM VASummary 

WHERE Free <> 0

_____________________________________________________________________________________________________________________________

32-bit laptop with SQL Server 2005 (32-bit obvously) and 2GB RAM:
Total avail mem, KB
340680
max free size, KB
120016

A 64-bit server with SQL Server 2008 x64 with 10GB RAM:
Total avail mem, KB
8579026976
max free size, KB
6708754816

** 64bit 환경에서는 더 이상 MemToLeave 영역 때문에 걱정 할 필요가 없겠네요 ^^
Come on 64bit so we can leave the mem….

http://blogs.msdn.com/b/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.asp

[MemToLeave 사이즈 변경 방법]

1. SQL Server 구성 관리자를 실행합니다.
2. ‘SQL Server 2005 서비스’를 클릭하고, 우측 패널에서 ‘SQL Server (<InstanceName>)’을 더블 클릭합니다.
3. ‘SQL Server (<InstanceName>)’ 속성창에서 ‘고급’ 탭을 클릭합니다.
4. ‘고급’ 탭에서 ‘시작 매개 변수’ 항목의 기존 값의 맨 앞이나 끝에 다음을 추가합니다.

값 예) -g512;

기존의 시작 매개 변수에 상기의 옵션을 추가하게 되면(;(semi-colon)으로 기존의 값과 분리해야 함) 다음의 예와 같은형태가 됩니다.

-g512;-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-eE:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-dE:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;

5. ‘확인’을 눌러 설정 작업을 완료합니다. 설정한 내용이 적용될 수 있도록 SQL서비스를 재시작합니다.

[참고 사이트]

SQL Server 서비스 시작 옵션 사용

http://msdn.microsoft.com/ko-kr/library/ms190737(v=SQL.100).aspx

Come on 64bit so we can leave the mem….

http://blogs.msdn.com/b/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.asp

SQL Server memtoleave, VAS and 64-bit

http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx

감사합니다.