4

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?

Ian_H
  • 1,674
  • 10
  • 17
Alf47
  • 981
  • 1
  • 9
  • 22

2 Answers2

3

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

Callie J
  • 492
  • 3
  • 15
Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
0

Mapping System Tables to System Views: https://msdn.microsoft.com/en-us/library/ms187997.aspx

A combination of sys.dm_exec_sessions & sys.dm_exec_requests is likely to contain most of what you need.

If you post your query we should be able help you to write a SQL2016-compliant version.

Gareth Lyons
  • 1,158
  • 8
  • 13