question

Ron 3 avatar image
Ron 3 asked

Memory Configuration (LazyWriter Errors)

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!

sql-server-2005memory
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
Are you using CLR modules?
0 Likes 0 ·

2 Answers

·
Oleg avatar image
Oleg answered

There is nothing to worry about the 2147483647. This number is simply the highest available int value. Try to add 1 to it and cast it to int to get "Arithmetic overflow error converting expression to data type int" error. This number simply instructs the database engince not to impose any limit on the amount of memory. This also means that there are occasions when it will try to reserve all the available memory on the box thus starving the OS. This could lead to the problems you describe. You should set the limit probably not to 6144 but to something somewhat smaller than that to ensure that SQL Sever never starves the OS.

Just wanted to add that Maximum Server Memory (in MB): 2147483647 is an out of the box default (needs to be set to something more reasonable after the installation).

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CFL_DBA avatar image
CFL_DBA answered

Since you have a 64-bit machine with 6 GB of RAM, are you using the 64-bit version of SQL Server 2005? If not, do you have AWE properly enabled?

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ron 3 avatar image Ron 3 commented ·
I am running SQL Server 64-bit. I do not have AWE enabled.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.