Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SQL Server 2005 Expand / Collapse
Author
Message
Posted Thursday, May 8, 2008 2:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:32 PM
Points: 30, Visits: 51
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.
Post #496951
Posted Thursday, May 8, 2008 3:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 13, 2011 2:21 AM
Points: 251, Visits: 208
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.

Post #496959
Posted Thursday, May 8, 2008 4:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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
Post #496984
Posted Thursday, May 8, 2008 4:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 16, 2008 3:07 PM
Points: 393, Visits: 52
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..."
Post #496989
Posted Thursday, May 8, 2008 4:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 871, Visits: 547
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.




Post #496997
Posted Thursday, May 8, 2008 5:25 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:35 AM
Points: 5,886, Visits: 13,044
The question is correct.

I got it wrong because I always refer to it as mssqlsystemresource and thought 'resource' was incorrect shorthand, but turns out microsoft refer to it as the resource database, so I have learnt something and in future will refer to it as 'resource', thus saving myself the effort of saying 'mssqlsystemresource' which does not roll off the tongue.

And this post gets me my point back :)


---------------------------------------------------------------------

Post #497014
Posted Thursday, May 8, 2008 6:14 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:31 AM
Points: 491, Visits: 225
I can see the debate over this question, however, I'm going to side with this statement:
"The physical file name of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf."

It's my opinion that the question's use of the shorthand name, which microsoft also uses in documentation, is ok.

Either way, I enjoy the community's input...gets me thinking in the morning.

source: http://msdn.microsoft.com/en-us/library/ms190940.aspx


Cheers,

Jeff
Post #497038
Posted Thursday, May 8, 2008 6:51 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:00 AM
Points: 871, Visits: 547
There's an excellent blog by Kalen Delaney on this subject :- http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

I think her query from sysdbreg table does still show some ambiguity in the database name though :)



Post #497066
Posted Thursday, May 8, 2008 7:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:54 AM
Points: 1,516, Visits: 2,722
I agree that Resource is a system database with the physical name mssqlsystemresource.mdf, in the sense that the system uses it. But I can also see a physical file named distmdl.mdf that is not mentioned in the msdn article, but apparently is used as a model for the distribution database if you set up replication, so isn't that also a system database?

OTOH if you open SSMS you can only see the four databases that most of us think of when you say 'system database'.

I think the msdn article needs some clarification.
Post #497115
Posted Thursday, May 8, 2008 8:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:25 AM
Points: 1,386, Visits: 404
According to my books online

System and Sample Databases
mdf Mastlog.ldf model Model.mdf Modellog.ldf msdb Msdbdata.mdf Msdblog.ldf tempdb Tempdb.mdf Templog.ldf The system databases are master, model, msdb, and tempdb. Note: The default location of the database and log files is Program Files ...
Source: Installing SQL Server 2005


No mention of the resource database nor does it appear in my SQL Server Management Studio under System databases hence it is not a "real" system database.

What I did find about the resource "database"
>>
The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
<<

-- Mark D Powell --
Post #497132
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse