Msg 802, Level 17, State 20, Line 1 There is insufficient memory available in the buffer pool.

  • Hi,

    I am rebuilding the indexes for a database that's 120GB on my laptop. My laptop has windows 7 64bit, 4GB Mem and 275GB HD. I am using 64bit SQL Server 2008 Standard edition R2. Currently I am using this as test environment as I have scheduled for an index rebuild on prod. env. for tonight.

    On my laptop I am getting the following error after running the rebuild index code for 4 hrs:

    Msg 802, Level 17, State 20, Line 1

    There is insufficient memory available in the buffer pool.

    the prod server is a SAN based DL385 with 8core processor and has sql server 2008 std ed. sp1. I have increased the size of tran log to 50GB for this database and will make sure I take a tran log backup before starting this rebuild job. Any ideas on what kind of issues I might face as I am not getting a chance to test it before implementing on actual prod. environment.

    Please provide me with your guidelines. Your help is much appreciated.

    Thanks in advance.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Just an FYI...I have the total index size as 30GB and I have increased my transaction log size to 50GB.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • Is this error not related to not setting a max memory setting for the SQL Server?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply