Lesson Learned #295:The specified buffer size is less than the minimum size.

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>)

Regards,

Continue reading...