SQL Server 2005

Question

SQL Server 2005

Viewing 15 posts - 16 through 30 (of 50 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

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 🙂

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

Jeff Deluca Ten Centuries Points: 1152 More actions · Answer 2

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

stevehindmarsh Ten Centuries Points: 1130 More actions · Answer 3

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 🙂

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

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.

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 5

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 --

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 6

I agree that asking about a Resource database is misleading and incorrect. In the system db .mdf files master.mdf refers to Master etc. so mssqlsystemresource.mdf should not be considered Resource but MSSQLSystemResource.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Not sure where you are looking in BOL, but my local copy matches the version at Microsoft, and it clearly discusses 5 system databases, including the Resource Database (mssqlsystemresource.mdf/mssqlsystemresource.ldf).

Here is the link to the BOL at Microsoft: http://msdn.microsoft.com/en-us/library/ms178028.aspx

I am curious, however, would we be having this same discussion if the physical names of master, model, msdb, and tempdb were sqlmaster, sqlmodel, sqlmsdb, sqltempdb but were still refered to as master, model, msdb, and tempdb in the documentation?

😎

SanjayAttray SSChampion Points: 13157 More actions · Answer 8

Prasad Bhogadi (5/8/2008)
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.

Resource is definitely a system database though not visible to users except a dedicated SA role/Login and is in read-only mode.

SQL DBA.

Jeff Deluca Ten Centuries Points: 1152 More actions · Answer 9

I agree that saying

mssqlsystemresource.mdf should not be considered Resource but MSSQLSystemResource

can probably be considered splitting hairs. :hehe:

My BOL talks about 5 system databases as well.

Cheers!

Jeff

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 10

Lynn Pettis (5/8/2008)
I am curious, however, would we be having this same discussion if the physical names of master, model, msdb, and tempdb were sqlmaster, sqlmodel, sqlmsdb, sqltempdb but were still refered to as master, model, msdb, and tempdb in the documentation?
😎

Good point. As a matter of fact, the physical name of msdb is actually msdbdata.mdf but nobody has complained about that.

ADDED:

Remember that a database has three names: database name, logical name, and file name; they can all be the same, or all different.

I have a customer using Microsoft Retail Management System (RMS) and the database name is "par_pos", logical name is "QSDB_Data", file name is "PAR_POS_Data.MDF"

Anders Pedersen SSChampion Points: 11410 More actions · Answer 11

Lowry Kozlowski (5/8/2008)
I agree that asking about a Resource database is misleading and incorrect. In the system db .mdf files master.mdf refers to Master etc. so mssqlsystemresource.mdf should not be considered Resource but MSSQLSystemResource.

File name does not have to match database name in any way of course.

Q -631159 Ten Centuries Points: 1077 More actions · Answer 12

I got the question wrong.

If you look at this article it is not described as a system database, but it is a required database that must accompany the master database; hence, the name Resource.

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

A link at the bottom of this article is for system databases. Resource is clearly defined as a system database here.

http://technet.microsoft.com/en-us/library/ms178028.aspx

If you refer to this article, is states quite clearly that the procedure "applies to all system databases except the master and Resource databases."

http://technet.microsoft.com/en-us/library/ms345408.aspx

All these articles are from technet.

I should have researched it before I answered. :angry:

Q

Please take a number. Now serving emergency 1,203,894

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 13

I missed the question because I always thought the resource database was called MSSQLSystemResource which is the same as the mdf file... so figured it was a trick question. After reviewing the link, it looks like I over thought myself and learned something in the process.

:satisfied:

David

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 14

I have always considered the resource database a system database. Hell, the system can't operate without it for a start.

And regardless of the various logical & physical names, I have always seen it referred to as "the resource database".

How many people out there have referred to databases by the application/system that uses it instead of the actual database/logical/physical name(s)? Like "the Citrix database(s)" or "the Sharepoint database(s)".

It's all semantics really. As long as the person writing the Q&A is aware of the possible interpretations it shouldn't be a problem. They can even play on/with it. :o)

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

MatchSQL.km Newbie Points: 3 More actions · Answer 15

How do the Resource db in the SQL2005?

http://www.MatchSQL.com