Lesson Learned #290: Elastic pool SQL server extended not able to start

When creating Extended Events (XEvents) the session memory limit is exceeded and either of the following error messages (Errors: 25746/25747) are returned:

• "An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo). Error: 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. (Microsoft SQL Server, Error: 25746)"
  

• "Error: Operation failed. 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)"
  

This results from the extended xevent session buffer memory. There is a cap on memory available to Extended Event sessions in Azure SQL Database:

• In single Azure SQL Database in the DTU purchasing model, each database can use up to 128 MB. This is raised to 256 MB only in the Premium tier.
  
• In single Azure SQL Database in the vCore purchasing model, each database can use up to 128 MB.
  
• In an elastic pool, individual databases are limited by the single database limits, and in total they cannot exceed 512 MB.
  

The memory is shared on a logical server level between all databases (for an elastic pool, the default limit of 512MB is shared between all databases in the elastic pool). When the buffer limit is reached, the error occurs, and it's not possible to create new extended event sessions.

If there are no sessions active on a particular single database (or database in an elastic pool), check sessions running on other databases under the same logical server using this T-SQL command: SELECT * FROM sys.dm_xe_database_sessions

Please, confirm active Xevent sessions and alter existing sessions to reduce the memory limit for them (or even stopping them if not needed) to free up space for new sessions.

Extended events in SQL Server

Create Event Session

Continue reading...