G’day,

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

while

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

Cheers

Martin.