0

I'm using workloadtools to try and capture a production extended events "replay trace" and have hit a problem. The tool is creating the extended events session but is unable to start it for one database (it works fine on another smaller db on the same server). I get this error message:

Operation failed. Operation will cause database event session memory to exceed allowed limit. Event session memory may be released by stopping active sessions or altering session memory options. Check sys.dm_xe_database_sessions for active sessions that can be stopped or altered. If no sessions are active on this database, please check sessions running on other databases under the same logical server. (Microsoft SQL Server, Error: 25747)

The tool uses a template file to define the extended events - see below, I've tried editing the max_memory value both up and down but without success.

max_memory in sys.dm_user_db_resource_governance shows 100 for the db in question, docs say this is a percentage. Looking at that dmv points out to me that the 2 dbs in question have different configurations and are actually in different elastic pools (although on the same server)

These values also seemed pertinent:

  • max_memory_grant 13
  • max_db_memory 26787738

Has anyone got a suggestion for what to try next?

CREATE EVENT SESSION [sqlworkload] ON {1}
ADD EVENT sqlserver.attention (
    ACTION( 
        package0.event_sequence, 
        sqlserver.client_app_name, 
        sqlserver.client_hostname, 
        sqlserver.database_id, 
        sqlserver.database_name, 
        sqlserver.{2}, 
        sqlserver.session_id, 
        sqlserver.sql_text
    )
    {0}
),
ADD EVENT sqlserver.rpc_completed (
    SET collect_data_stream = (0),
    collect_output_parameters = (1),
    collect_statement = (1) 
    ACTION(
        package0.event_sequence, 
        sqlserver.client_app_name, 
        sqlserver.client_hostname, 
        sqlserver.database_id, 
        sqlserver.database_name, 
        sqlserver.{2}, 
        sqlserver.session_id
    ) 
    {0}
),
ADD EVENT sqlserver.sql_batch_completed (
    SET collect_batch_text = (1) 
    ACTION(
        package0.event_sequence, 
        sqlserver.client_app_name, 
        sqlserver.client_hostname, 
        sqlserver.database_id, 
        sqlserver.database_name, 
        sqlserver.{2}, 
        sqlserver.session_id
    ) 
    {0}
),
ADD EVENT sqlserver.user_event(
    ACTION(
        package0.event_sequence, 
        sqlserver.client_app_name, 
        sqlserver.client_hostname, 
        sqlserver.database_id, 
        sqlserver.database_name, 
        sqlserver.{2}, 
        sqlserver.session_id
    ) 
    WHERE [sqlserver].[like_i_sql_unicode_string]([user_info],N'WorkloadTools%')
)
WITH (
    MAX_MEMORY = 4960 KB,
    EVENT_RETENTION_MODE = NO_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = PER_CPU,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);


ALTER EVENT SESSION [sqlworkload] ON {1} STATE = START;
0
1

So the answer was to check every database in the elastic pool for extended event sessions and then stop and delete the few running ones I found. After that a retry worked. The hint was in the error message :-

Check sys.dm_xe_database_sessions for active sessions that can be stopped or altered.

I'd like to claim I wrote some smart powershell to iterate through every database - but actually I did a LOT of clicking in SSMS (Powershell next time).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.