Post reply

Resource database id.

  • July 13, 2009 at 12:29 am

    #208117

    Guys

    The ID of the Resource database is always 32767.

    How to confirm whether the database id mentioned above is correct?

    I fired the below query, but unable to get any result!!

    select * from master.dbo.sysdatabases

    where name ='resource'

    Any help is highly appreciated!

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 12:59 am

    #1023686

    Hi

    Resource Database is a hidden and Read-only DB available in SQL Server 2005 onwards

    All the System objects are stored in this database only

    No Changes can be made on this database and this database can not be queried directly in Sysdatabases.

    Product version can be obtained for this database as

    oSelect Serverproperty(‘ResourceVersion’)

    Dbs are in the path

    ~MSSQL\DATA\mssqlsystemresource.ldf

    ~MSSQL\DATA\mssqlsystemresource.mdf

  • July 13, 2009 at 1:06 am

    #1023688

    How to confirm that the database_id of the resource db is 37267?

    Select Serverproperty('ResourceVersion') => Tells about the version of the "resource db"..

    Any query explicitly can be fired to get the resource database id??

    Do you know?

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 1:23 am

    #1023689

    Hi sourav,

    Resource databaseid is 32767 by default. You cannot change it or find it.

    BOL: The ID of the Resource database is always 32767. Other important values associated with the Resource database are the version number and the last time that the database was updated.

    Refer: http://msdn.microsoft.com/en-us/library/ms190940(SQL.90).aspx

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • July 13, 2009 at 1:25 am

    #1023690

    According to BOL: "The ID of the Resource database is always 32767."

    No reason for it to change.

    One way to verify it would be to start the sql server in single user mode, connect to the resource database and fire:

    select DB_ID() as [Database ID];

    -Harshal.

    Harshal

  • July 13, 2009 at 1:26 am

    #1023691

    Just try this

    select distinct d.name

    from sys.dm_os_buffer_descriptors b with(nolock)

    join sys.databases d with(nolock)

    on b.database_id = d.database_id

  • July 13, 2009 at 1:37 am

    #1023694

    thanks for writing the below query..

    Seems after executing the provided query,if I query the DM: sys.dm_os_buffer_descriptors I can get the database_id.

    Is it?

    select top 10 * from sys.dm_os_buffer_descriptors

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 1:44 am

    #1023697

    Ofcourse,,

    This syste view would return information about all the data pages that are currently in the SQL Server buffer pool

  • July 13, 2009 at 1:50 am

    #1023705

    Awesome!! Thanks,

    Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply