SQL Server 2008 returns "Memory limit greater than 10,240 KB for a buffered query"

Question

I am trying to populate an HTML table with some data from SQL Server 2008 r2, the controller (php_sqlsrv) is working fine, the tables are filling up very well, but when I try to get 2000 or more rows (maybe less) it crashes and displays this message:

SQL error: array ([0] => array ([0] => IMSSP [SQLSTATE] => IMSSP [1] => -59 [code] => -59 [2] => 10240 KB memory limit exceeded for buffered request [message] => 10240 KB memory limit exceeded for buffered request))

How can i fix this? Is this a PHP or sqlsrv problem? Can I fix this from SQL Server Management Studio?

Answer 1

Change the setting in php.ini.

Section: sqlsrv

Directive: sqlsrv.ClientBufferMaxKBSize.

Answer 2

add two lines in php.ini

extension=php_pdo_sqlsrv_55_ts.dll extension=php_sqlsrv_55_ts.dll client_buffer_max_kb_size = '50240' sqlsrv.ClientBufferMaxKBSize = 50240 

Answer 3

You can also change the settings at run time if you do not want the production server to modify php.ini (check if this is applicable for your hosting).

Update the code with the following lines:

 ini_set('memory_limit','256M'); // This also needs to be increased in some cases. Can be changed to a higher value as per need) ini_set('sqlsrv.ClientBufferMaxKBSize','524288'); // Setting to 512M ini_set('pdo_sqlsrv.client_buffer_max_kb_size','524288'); // Setting to 512M - for pdo_sqlsrv 

To check if your server supports this, try printing the values ​​after installation above.

 echo ini_get('memory_limit'); echo ini_get('sqlsrv.ClientBufferMaxKBSize'); echo ini_get('pdo_sqlsrv.client_buffer_max_kb_size'); 

The new values ​​should be the ones we set in in__set (). In addition, the server does not support configuration changes at run time.