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!
Microsoft
