Currently, only the buffer pool takes action when SQL Server receives these notifications. When it receives a notification, SQL Server has to iterate through the whole buffer pool and discover the address for each allocated buffer. Then, SQL Server uses the QueryWorkingSetEX API to check whether any of the memory pages that back the data page is marked as bad. The PSAPI_WORKING_SET_EX_BLOCK output structure that corresponds to this memory page will have its member bad set to 1 if there is any damaged reported.
If that buffer pool or data page is currently not changed or not processing I/O, SQL Server can discard and de-commit the data page. Then, SQL Server logs the following message:
When queries require that data page again, the buffer pool can read the data page back from disk and bring the contents back to the buffer pool. It is also possible for the on-disk version of the page to be in a damaged state. In that case, SQL Server may log additional errors such as error 824.
If the bad page is used not by the buffer pool but by some other cached object or structure, SQL Server logs the following message:
If the server is reporting memory errors, you should contact the computer hardware vendor and perform appropriate actions such as performing memory diagnostics, updating BIOS and firmware, and replacing bad memory modules.
The following two extended events are available starting with SQL Server 2012. They are called for each page that is either fixed or identified as corrupted but cannot be fixed.
You can use SQL Server trace flag 849 to keep SQL Server from registering with the operating system for memory error notifications. However, be aware that trace flag 849 will disable SQL Server from receiving bad memory notifications from operating system. Therefore, we do not recommend that you use this trace flag under typical circumstances.
Also, be aware that, by default, SQL Server will receive these notifications on supported hardware.
You should also be aware that when SQL Server registers for these memory error notifications, the lazy writer system process does not perform constant page checks. For more information about constant page checks, click the following article number to view the article in the Microsoft Knowledge Base: