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?
Best Answer
Unfortunately, Microsoft is aware of the lack of replacements. You can upvote & watch these Connect items for more details:
Deprecation of sysprocesses - DMV's doesn't fully replace all columns - by Tony Rogerson SQL
There is no real alternative to master.dbo.sysprocesses - by GV1973