Lesson Learned #295:The specified buffer size is less than the minimum size.
Published Jan 20 2023 10:13 AM 2,554 Views

We recently found an issue enabling an Extended Event that our customer got the following error message: Msg 25632, Level 16, State 29, Line 1 - The specified buffer size is less than the minimum size. The minimum allowed size is xzy bytes (where xyz is a number in KB)

 

In this situation, I would like to suggest changing the MAX_MEMORY parameter of the extended event to, at least, the value in megabytes specified in xyz (previously in KB). 

 

For example, we have this extended event without specifying MAX_MEMORY. 

 

 

 

CREATE EVENT SESSION [ADS_Standard_Azure4] ON DATABASE
					ADD EVENT sqlserver.attention(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
					ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id,sqlserver.client_hostname)),
					ADD EVENT sqlserver.login(SET collect_options_text=(1)
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id,sqlserver.client_hostname)),
					ADD EVENT sqlserver.logout(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id,sqlserver.client_hostname)),
					ADD EVENT sqlserver.rpc_completed(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
					ADD EVENT sqlserver.sql_batch_completed(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
					ADD EVENT sqlserver.sql_batch_starting(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
					ADD TARGET package0.ring_buffer(SET max_events_limit=(1000))
					WITH (EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) 

 

 

 

If you run this TSQL command and you have the error Msg 25632, Level 16, State 29, Line 1 - The specified buffer size is less than the minimum size. The minimum allowed size is xzy bytes (where xyz is a number in KB). Including the following parameter value MAX_MEMORY the execution of this extended event will be fine. cap on memory available 

 

 

 

CREATE EVENT SESSION [ADS_Standard_Azure4] ON DATABASE
					ADD EVENT sqlserver.attention(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
					ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id,sqlserver.client_hostname)),
					ADD EVENT sqlserver.login(SET collect_options_text=(1)
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id,sqlserver.client_hostname)),
					ADD EVENT sqlserver.logout(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.username,sqlserver.session_id,sqlserver.client_hostname)),
					ADD EVENT sqlserver.rpc_completed(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
					ADD EVENT sqlserver.sql_batch_completed(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
					ADD EVENT sqlserver.sql_batch_starting(
						ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.username,sqlserver.query_hash,sqlserver.session_id,sqlserver.client_hostname)
						WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
					ADD TARGET package0.ring_buffer(SET max_events_limit=(1000))
					WITH (EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF,MAX_MEMORY=<xyzcalculatedInMB>)

 

 

We found that using ADS and enabling SQL Profiler you could find out the following error message:

 

- 'Object reference not set to an instance of an object'
- 'An exception occured while exectuting a Transact-SQL Statement or batch'

 

Enjoy!

1 Comment
Version history
Last update:
‎Jan 31 2023 07:27 AM
Updated by: