I noticed something odd recently when looking at sys.dm_exec_requests. Specifically the system processes listed in that DMV – well at least I thought it was odd
Normally, I join with sys.dm_exec_sessions and filter out anything that is a system process (WHERE [sys].[dm_exec_sessions].[is_user_process] = 1 )
However, this time I quickly wrote the query and didn’t filter anything, I was actually looking for connections to a particular database when I noticed that the system processes where reporting their database_id as either 1 or 0 – interesting.
Buy, hey, see it for yourself
Run the query below -
USE [master]; GO select [der].[session_id], DB_NAME([der].[database_id]) [Database Name], [der].[database_id], [des].[is_user_process], [der].[command] from [sys].[dm_exec_requests] [der] JOIN [sys].[dm_exec_sessions] [des] ON [der].[session_id] = [des].[session_id] WHERE [des].[is_user_process] = 0 ORDER BY [der].[session_id] ASC; GO
The number of rows containing a database_id of 0 and 1 for system processes actually vary on different instances.
Now try this
USE [master]; GO select db_name(0) [db_name(with arg)] , 0 [db_name() argument] UNION ALL select db_name(1) , 1 GO
and I get “master” reported in both cases – so initially I thought that giving the db_name() function a value of 0 always returned ‘master’ – not so. – it actually is just reporting the current database, it just so happens that I was in master at the time
SELECT DB_NAME(0) will return the name of the current database.
SELECT DB_NAME() will also return the name of the current database.
SELECT DB_NAME(”) will also return the name of the current database – note the empty string
SELECT DB_NAME(‘sdfdf’) – replace ‘sdfdf’ with any string you like (even a valid database name) and it returns an expected conversion error
so, when you see a database id of 0 in sys.dm_exec_request - it really means “the database you are running in”
0 (zero) seems to be a magic number that means “the current database” – try it for yourself, change the database name in the USE statement to anything you like
USE [tempdb]; GO select db_name(0) [db_name(with arg)] , 0 [db_name() argument], db_name() [Current Database], CASE (db_name()) WHEN db_name(0) THEN 'Same' ELSE 'Different' END [Name correspondes as] UNION ALL select db_name(1) , 1 , db_name(), CASE (db_name()) WHEN db_name(1) THEN 'same' ELSE 'Different' END GO
Have a great day