SQL Server 2005

  • ginni.sinha

    Old Hand

    Points: 343

    Comments posted to this topic are about the item SQL Server 2005

  • brewmanz

    SSCommitted

    Points: 1575

    There seems to be a conflict in the documentation about what constitutes a "database". Seems that Resource is a *special* system database. Whether it's truly a system database is debatable ...

    So I reckon Resource is *not* a system database using following logic:

    * SQL Server 2005 BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/60a93880-62f1-4eda-a886-f046706ba90c.htm states for sys.sysdatabases

    "Contains one row for each database in an instance of Microsoft SQL Server 2005. When SQL Server is first installed, sysdatabases contains entries for the master, model, msdb, and tempdb databases."

    No Resource there.

    * Also, trying the following in studio

    use msdb

    use tempdb

    use Resource

    use master

    it failed with:

    "Could not locate entry in sysdatabases for database 'Resource'. No entry found with that name. Make sure that the name is entered correctly."

    ----

    So if it doesn't look like a duck and can't quack like a duck, I reckon it's not a duck.

  • Lynn Pettis

    SSC Guru

    Points: 442235

    True, the Resource database is not listed in sys.databases; however, the documentation does refer to it as a special, read-only database that must be located in the same directory as the master database and that the objects contained are viewed thru the sys schema.

    It is a system database.

    😎

  • stevehindmarsh

    Ten Centuries

    Points: 1130

    From a SQL Server Errorlog during startup :-

    Starting up database 'mssqlsystemresource'.

    The question is wrong - 'mssqlsystemresource' could be referred to as a system database, and I think there are arguments for and against that...

    But there's no such database as 'resource'.

    The question is misleading\incorrect.

  • abhijeetv

    SSCommitted

    Points: 1945

    Resourse database is not system database available directly so the answer is wrong. Correct answer should be master,model,msdb and tempdb only!

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    The question is correct.

    And the provided link should be evidence enough.

    Best Regards,

    Chris Büttner

  • brewmanz

    SSCommitted

    Points: 1575

    Christian Buettner (5/8/2008)


    ...

    And the provided link should be evidence enough.

    To take the first bit of evidence presented and then dismiss all other evidence that disagrees is a bit one-eyed. If I had submitted the Q and excluded Resource as system database with the evidence of my BOL link, would that link then be "evidence enough" to say that it isn't one?

    The provided link is prima facie evidence, but conflicting evidence is now being presented.

    The discussion seems to centring around "what makes a system database, and what name would it called by?"

    The BOL has contradictory statements in it (surprise!). I have provided BOL and empirical evidence that it fails to be a system database. Others also have submitted info both for and against the Q's stance. Weigh up the evidence, yes; argue against it even. But please, don't dismiss it just because it disagrees with someone's viewpoint; you have to have a better reason than that.

  • humbleDBA

    Hall of Fame

    Points: 3440

    And also the following link http://msdn.microsoft.com/en-us/library/ms190940.aspx

    Plus, Henderson K (2007), SQL Server 2005 Practical Troubleshooting The Database Engine, pp51-52.

  • Prasad Bhogadi

    SSCrazy Eights

    Points: 9235

    I am not sure if resource is a system database. From the article in the question it does not cement the answer that resource is a system database.

    Prasad Bhogadi
    www.inforaise.com

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    brewmanz.sqlservercentral (5/8/2008)


    Christian Buettner (5/8/2008)


    ...

    And the provided link should be evidence enough.

    To take the first bit of evidence presented and then dismiss all other evidence that disagrees is a bit one-eyed. If I had submitted the Q and excluded Resource as system database with the evidence of my BOL link, would that link then be "evidence enough" to say that it isn't one?

    The provided link is prima facie evidence, but conflicting evidence is now being presented.

    The discussion seems to centring around "what makes a system database, and what name would it called by?"

    The BOL has contradictory statements in it (surprise!). I have provided BOL and empirical evidence that it fails to be a system database. Others also have submitted info both for and against the Q's stance. Weigh up the evidence, yes; argue against it even. But please, don't dismiss it just because it disagrees with someone's viewpoint; you have to have a better reason than that.

    Hello brewmanz,

    I agree, that the missing info about the resource database can be misleading in the link you provided.

    But be honest: The article provided by the QOTD explicitly defines system databases. Your link does NOT explicitly define system databases. It only explains the compatibility view.

    Therefore to me it is pretty clear that the resource database IS a system database.

    Best Regards,

    Chris Büttner

  • henry-f

    SSC Rookie

    Points: 34

    if the master database has no reference to the "resource" database.

    select * from master.sys.sysdatabases

    then, mssqlsystemresource.mdf is a file that happens to have a .mdb extension.

  • Biggles-581128

    Old Hand

    Points: 303

    A database that remains hidden and isn't even reported in the master.sys.sysdatabases has got to be a System Database!! It seems only the system can do anything with it 🙂

    Nice question, get's you thinking about what is under the hood.

  • majorbloodnock

    SSCrazy Eights

    Points: 9267

    It's a file that contains data. What is a database if not that?

    It's installed as part of an instance, and used by SQL Server. What is a system database if not that?

    Microsoft (the people who designed the product) state unequivocally that they regard it as a system database. Who has the right to decide on its classification if not them?

    It's not like other SQL databases. So what? How does that make it less of a database so long as the above criteria have been met?

    Semper in excretia, sumus solum profundum variat

  • Arno H. Janssen

    SSC-Addicted

    Points: 487

    The Resource Database is a System Database. Extract from SQL Server 2005 Administration (under System Databases): "...It is new to SQL Server 2005 and contains all the read-only critical system tables, metadata, and stored procedures that SQL Server [2005] needs to run... The Resource database cannot be seen through Management Studio... You can connect to the database under certain single-user mode conditions by typing the command USE MSSQLSystemResource..."

  • stevehindmarsh

    Ten Centuries

    Points: 1130

    Quoting from above..

    "You can connect to the database under certain single-user mode conditions by typing the command USE MSSQLSystemResource..."

    So, the question should have named the database correctly as MSSQLSystemResource.

    We all seem agree there is a hidden 'resource' which may or may not qualify as a database - my point earlier is that the question is vague\incorrect in the name.

    Microsoft documentation refers to this as the resource database.

    But, if the USE statement needs to have MSSQLSystemResource - then the database name is MSSQLSystemResource. The question is incorrect.

    If USE Resource will never work. Then the question is incorrect.

Viewing 15 posts - 1 through 15 (of 51 total)

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