The SQL 2016 Data Migration Assistant (Upgrade Advisor) is saying that I need to stop using old system table references. Namely, it wants me to stop using sysdatabases and sysprocesses.
I have some code that's logging session information based on data from sysprocesses. Included in that log is the database ID of the spid (session_id).
I have been searching online and have found only two possible replacements, neither of which work fully due to reasons explained below.
Option 1 (sys.dm_tran_locks) - This option seems to work rather reliably if I link back to sys.dm_exec_sessions but I noticed that it will never return a result if the session_id's database context is master or tempdb. That means I will not be able to reliably fetch the resource_database_id for any session_id that is using master or tempdb. I also cannot just assume it since there are at least two possible contexts to choose from.
Option 2 (sys.dm_exec_requests) - This option only works on session_id's that are actively running. Idle/suspended session_id's do not appear in the list.
If I cannot get the dbid for session_id's that are suspended and/or are using the master database context then I will have a gap in my logs.
Is there any other way to reliably fetch this information without the use of sys.sysprocesses?