Tuesday, April 3, 2012

vCenter SQL Max Server Memory

We encountered an error on our vCenter 5 server today

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.

Our vCenter VM is Windows 2008 (64 bit) with MS SQL 2008.  The VM is allocated 16Gb RAM - but I've noticed for a while no matter how much memory is allocated, the SQL Server process will eventually grow to fill the allocated memory.  And this error got me digging for a solution.

As it turns out, in SQL 2008, the memory allocation for SQL is dynamic and this growth of the SQL Server memory use is expected, UNLESS you set the "Maximum Server Memory"

This is done simply via the SSMS (SQL Server Management Studio):

Right Click Database -> Properties -> Memory

The change is effective immediately - in taskmgr I saw our SQL Server memory usage DROP from 13Gb to 10Gb.  By limiting the SQL memory, this will actually help optimize other vCenter processes and jobs (like the vCheck5 report - which has been taking > 3 hours lately?)  Tune in tomorrow to find out!

No comments: