none
SQL Server 2005 Pagefile Problem

    Question

  •  

    SQL Server 2005 64-bit Standard Edition SP2 on Windows Server 2003 64-bit

     

    Dedicated SQL box (dual proccessors - dual cores, 16gb RAM) with single SQL instance has been running a year without problem - SQL Max memory at 14gb. Pagefile set at 30gb.

     

    Out of the blue the pagefile has begun growing to where it takes up all space and then everything crashes.  All is well after a server restart but within 2 weeks the pagefile grows to maximum size and again crashes the system.  I lowered SQL max memory to 12gb and relocated the pagefile to a larger drive and increased it's limit to 60gb - but it continues to grow -as of today it's at 51+gb.

     

    Utility program shows that SQL is what is consuming the pagefile....

     

    Based on my understanding with a dedicated server there should be minimal pagefile use in this configuration - we have small number of concurrent sessions - most SQL memory use is for buffer cache....

     

    Any ideas on what might have started causing this or what to try to fix it??  No SQL config changes have been made. 

     

    Thanks in advance!

    Wyatt

    Tuesday, August 05, 2008 2:04 PM

Answers

All replies

  • Did you check that the service account that SQL runs under has been granted the lock pages in memory privilege?
    This will help prevent SQL paging
    Also be sure to set a min memory for SQL (e..g 4GB), as well as the max

    Jack
    Wednesday, August 06, 2008 3:27 PM
  • This is the Standard version which ignores the lock pages in memory switch.

     

    Since this is a single-instance, dedicated machine, SQL was originally configured with 10gb min and 14gb max.  Since these problems began occurring, I've since lowered the max to 12gb.....leaving 4gb for OS.

     

    After running out of paging file space with a 30gb paging file, we bumped the max paging file size to 60gb and restarted the server early this morning when the paging file had reached 58gb.

     

    Paging file size is now at minimum but pagefile % usage is gradually increasing so once it gets in the 90% range the page file will begin growing again - cyclical manifestation of the issue....

     

    Wednesday, August 06, 2008 7:03 PM
  •  

    OK - found it.  Altiris running on 64-bit platform.  We removed Altiris and pagefile use went to nothing...

     

    Symantec link below talks about it being a clustered Windows Server 2003 64-bit issue but in our case this was happening on a non-clustered server....

     

    Altiris knowledgebase:

    https://kb.altiris.com/display/1/index.asp?c=&cpc=&cid=&cat=&catURL=&r=0.4653131

     

    Search for 36505 (knowledgebase article number).

     

    Back to our regularly scheduled activities!

    Thursday, August 07, 2008 4:33 PM