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?

Improve this question
9
  • what are you trying to achieve .e.g. find blocking or locks ? There can be a way to achieve if you let us know what you mean by ` have some code that's logging session information based on data from sysprocesses.` ? e,g. using sys.dm_tran_locks etc
    – Kin Shah
    Feb 8, 2017 at 19:02
  • How reliable / meaningful is the database_id anyway? If someone is in master and querying a join between two databases or a union across all databases... what difference does it make? Why is this information necessary for sessions that are idle at query time? Their last query may have been against database 1 but their next query might be against database 12.
    – Aaron Bertrand
    Feb 8, 2017 at 19:30
  • @Kin I have one procedure that is logging blocking and another that is logging some audit information about currently logged on sessions.
    – Alf47
    Feb 8, 2017 at 19:36
  • @AaronBertrand but why wouldn't I be able to obtain the current session's database context? I was able to do it in the past and I can still get it from sysprocesses? Whether they 3 part reference another database and/or plan to query another database in their next batch I should be able to obtain the current session's context using the DMVs reliably.
    – Alf47
    Feb 8, 2017 at 19:39
  • My point is I don't know how reliable or useful the info you're getting from sysprocesses is ... okay, database_id has a value, but is it the right value? Are you actually using that value for anything (other than not violating a NOT NULL constraint in your logging table)? The blocking information you're logging, for example, has the information about database / schema / object already. Why do you care what's in the database_id column? If a lock in database 1 is blocking a query running in database 2 joining tables in databases 3 and 4, what 1 value of database_id do you want?
    – Aaron Bertrand
    Feb 8, 2017 at 19:41

2 Answers 2

Reset to default
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

Improve this answer
1
  • Yes, unfortunately this is all I was able to find. I was hoping there was more information that i was missing. Thank you for confirming my suspicions.
    – Alf47
    Feb 8, 2017 at 19:37
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.