SQL Server 2005, MS Server 2008 64 Bit, 6GB Memory, 1 Instance.
This morning I came into work to find that half of my SQL databases were not accessible. I promptly went to my SQL server and found that the processor was maxed out. I then went into the logs and found an error:
LazyWriter: warning, no free buffers found
This was followed by a bunch of MEMORYCLERK messages with VM Reserved, VM Committed, etc...
This is a new SQL server, 3 weeks old, that replaced an existing SQL server.
I did a lot of research on this error and found that my tuning and customization may not be set correctly. So I took a look at my old SQL server to start comparing more settings. There is one setting that struck me right away!!
The memory for my only instance has 2147483647 MB set as the Maximum server memory! I have no idea how this was set, but it seems impossible considering I only have 6GB of physical memory. Even when you add the page file, which is set at 6GB-9GB, the 2147483647MB doesn't even come close. So I looked at the old SQL Server, which had only 2GB, and the maximum memory setting for that instance was set to 2816.
So my question is this. What in the world set this memory value so high?
Should I change the memory value to 6144 MB(6GB)?
And most important... do you think this will resolve this LazyWriter error that puts my SQL Server to it's knees for 10 minutes?
One more detail... this happened twice today. Once at 7:38 and again at 10:08. Luckily for me... it hasn't happened again, but I'm not holding my breath.
As always, thank you for your suggestions!