The dmv sys.dm_clr_appdomains is a great way to find information about the appdomains currently loaded in the system, but it doesn’t help if the appdomain in question has already been unloaded. For those DBAs who want to track their appdomains like their teenage kid, the RING_BUFFER_CLRAPPDOMAIN data in sys.dm_os_ring_buffers should do the trick.
Important note: the dmv sys.dm_os_ring_buffers is undocumented and as such might change or go away in a future release. You shouldn’t rely on this behavior remaining constant, however for now it is a useful tool.
The following query shows all of the most recent appdomain state transitions, giving you the ability to see not only what state your appdomain is in now, but also what state it was in either 5 minutes ago or at 3 am last night.
rec.value(‘/Record/AppDomain/@address’, ‘nvarchar(10)’) as address,
rec.value(‘/Record/AppDomain/@dbId’, ‘nvarchar(4)’) as dbid,
rec.value(‘/Record/AppDomain/@ownerId’, ‘nvarchar(5)’) as ownerid,
rec.value(‘/Record/AppDomain/@type’, ‘nvarchar(32)’) as type,
rec.value(‘/Record/AppDomain/State’, ‘nvarchar(32)’)as state
SELECT timestamp, cast(record as xml) as rec
WHERE ring_buffer_type = ‘RING_BUFFER_CLRAPPDOMAIN’
) T(timestamp, rec)
ORDER BY timestamp DESC
There is a lot more flexibility you can do with this information depending on your needs. If the appdomain is currently loaded, you can determine which appdomain it is by joining sys.dm_clr_appdomains on the address field. Dbid and ownerid can be used to determine which database and which user the appdomain belongs to.
Tomorrow, I’ll describe what the different appdomain states mean.
– Steven Hemingray