Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

Backup made easy with SQL Safe Backup

SQL Server Performance Monitoring in the Cloud

Monitor and Troubleshoot SQL Server Performance

5 ways monitoring SQL Server Agent Jobs can make your job easier

Deploying Applications in Azure without Configuration Limitations

SQL Server Lazy Writer Issue Warning No Free Buffers Found

MSSQLTips author Matteo Lorini By:   |   Read Comments (5)   |   Related Tips: More > Database Administration

This tip is intended to help someone who is or will be facing a no free buffer found issue with the SQL Server LazyWriter.  Recently, we experienced some unknown issues with our SQL Server and after inspecting the error log file we noticed the following errors: "SQL Server has encountered 50 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file..." and "LazyWriter: warning, no free buffers found."  These error messages were followed by a SQL Server memory dump file like SQLDump0030.mdmp in the file system.

The first error (SQL Server has encountered 50 occurrence(s) of I/O requests..) is quite simple to understand. It is basically an indication that the I/O subsystem is not performing as it should. The second error (LazyWriter: warning, no free buffers found) is less intuitive. Unfortunately, the exact meaning of "no free buffers found" warning is harder to figure out.

Since we were receiving these error messages and performance seemed to degrade on our production SQL Server we decided to engage Microsoft Premier Support. To our surprise, our issue was escalated automatically from a level I to a level III support engineer.  After the SQL Dump files were analyzed we received an explanation of what was wrong with our environment.

By examining the SQL Dump files, Microsoft support found that SQL Server was running out of Buffer Pool memory. There was not enough memory to handle the data pages for the queries to process these efficiently. At peak or close to peak load, this forced the SQL Server lazy writer to run more often to clean out dirty pages from memory to disk. This in turn led to a higher disk I/O and the reporting of disk I/O processes taking longer than 15 seconds to complete. Eventually, SQL Server ended up with a scenario of a non-yielding IOCP (I/O completion port), because the thread is having to wait a long time for the disk I/O to complete. This is what caused the SQL Server dumps.

The buffer pool memory is for the storage of compiled query plans. In our circumstance, there were many queries with relatively large query plans that were stealing memory from the buffer pool. The longer SQL Server ran, the more memory what was stolen from the buffer pool and the more the lazy writer had to run. Eventually, SQL Server runs out of memory and the lazy writer cannot run.  This results in the message "LazyWriter: warning, no free buffers found."

Next Steps

Last Update: 2/15/2010

About the author
MSSQLTips author Matteo Lorini
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Tuesday, February 16, 2010 - 5:19:58 AM - dmining06 Read The Tip

SQl was running a 3rd party app. It used only store procedure. Server was set to manage memory dynamically. It was a SQL 2005 32 bits. We have to enable AWE, Grant "Lock Page in Memory" local security policy right to the SQL Server Service account and set the MAX Memory to 4GB. Total server memory was 6GB.




Tuesday, February 16, 2010 - 5:17:38 AM - dmining06 Read The Tip

 It was a SQL 2005 32 bits. We have to enable AWE, Grant "Lock Page in Memory" local security policy right to the SQL Server Service account and set the MAX Memory to 4GB. Total server memory was 6GB.

Was not able to rewrite code because is a thrid party application.



Monday, February 15, 2010 - 1:51:55 PM - aspiringgeek Read The Tip

Kalen Delaney et al speak of the so-called Lazy Writer & free buffers in SQL Server 2008 Internals.  Very cool topic.

Monday, February 15, 2010 - 11:53:47 AM - aprato Read The Tip

Which version and service pack of SQL Server did you experience this?  Do your applications run a lot of non-prepared, ad-hoc SQL?  This can also flood the buffer pool with a plans that may have little chance of being re-used.

I'd be interested in the solution also.  If SQL Server was being allowed to manage memory dynamically, then I suspect more memory was added.

Monday, February 15, 2010 - 7:58:27 AM - --cranfield Read The Tip

Hi Matteo. Thank you for writing up this real world problem.

You don't mention what the solution was. You say that there was not enough memory but how did you go about addressing this problem? Did you re-write some code, tune the memory settings or add more memory?



Sponsor Information