locked
database_id of 0 : master database appears to have id of 0 and also of 1 RRS feed

  • Question

  • Hi,

    I am new to SQL server and am attempting to write some scripts against the DMViews.

    Any help, insight, into this issue would be greatly appreciated.

    I am surprised to see a database with database_id of 0 and also 1 at the same time.

    Here is the evidence of what I am seeing.

    When I execute a query against sys.dm_exec_sessions I see a database_id of 0 for some connections.

    This seems strange to me.

    I thought the database Id's started at 1.

    Also it appears that the master database can somehow have a database_id of 0 and also 1.

    e.g.

    SELECT DB_ID(DB_NAME ('0')) and SELECT DB_ID(DB_NAME ('1'))

    both return "master"

    Here is some more evidence of this below from view sys.dm_exec_sessions where you can see that 15 connections are listed with database_id of 0 and 38 are listed as listed as database_id 0f 1. However both resolve (via DB_NAME) to the database name master.

    Refer below...

    SELECT DB_NAME(database_id) dbname, database_id,  COUNT(*) AS numbersessions
    FROM sys.dm_exec_sessions GROUP BY DB_NAME(database_id),database_id
    ORDER BY 2,1

    db_name database_id count(*)

    master 0 15
    master 1 38

    Looking in view sys.databases I cannot see the database_id of 0.

    SELECT database_id FROM FROM sys.databases

    database_id

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    Wednesday, February 19, 2020 12:43 PM

Answers

  • That are all internal system processes; all with spid <= 50

    SELECT *
    FROM sys.sysprocesses
    WHERE dbid = 0


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by ukWoody Thursday, February 20, 2020 11:23 AM
    Thursday, February 20, 2020 9:37 AM

All replies

  • Also it appears that the master database can somehow have a database_id of 0 and also 1.

    No, the function DB_NAME() returns the name of the current database when you pass 0 or nothing to it; connect to a different database then master and you will see it, example

    USE TempDB
    GO
    select db_name(), db_name(0), db_name(1)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, February 19, 2020 1:10 PM
  • The DB_NAME function expects an integer rather than a string, although the value '0' will be implicitly converted to integer. DBNAME(), DBNAME('0'), and DBNAME(0) all return the current database context so it seems you were running the queries in the master database. You'll get different results if you run those from a different DB.

    You probably want to exclude system processes when querying sys.dm_exec_sessions:

    SELECT DB_NAME(database_id) dbname, database_id,  COUNT(*) AS numbersessions
    FROM sys.dm_exec_sessions 
    WHERE is_user_process = 1
    GROUP BY DB_NAME(database_id),database_id;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, February 19, 2020 1:19 PM
  • Thanks Olaf.

    I now see that db_name(0) will return the currently "used" database.

    What I still unsure about is that there are sessions "listed" within sys.dm_exec_sessions with a database_id of 0.

    I.e.

    SELECT database_id, COUNT(*) number_of_sessions

    FROM sys.dm_exec_sessions

    GROUP BY database_id

    database_id number_of_sessions
    0 15
    1 40
    2 1

    Any further help would be greatly appreciated.


    • Edited by ukWoody Thursday, February 20, 2020 9:32 AM
    Thursday, February 20, 2020 9:29 AM
  • Thanks Dan, much appreciated.
    Thursday, February 20, 2020 9:33 AM
  • That are all internal system processes; all with spid <= 50

    SELECT *
    FROM sys.sysprocesses
    WHERE dbid = 0


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by ukWoody Thursday, February 20, 2020 11:23 AM
    Thursday, February 20, 2020 9:37 AM
  • Thanks a lot Olaf, much appreciated
    Thursday, February 20, 2020 11:24 AM