Administration

Question

Post reply

Administration

Viewing 13 posts - 16 through 27 (of 27 total)

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

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 1

same thing here.. was not expecting that.. this is the type of the question that normally show up in exams..:-D

you got me..:w00t:

Cheers,
John Esraelo

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 2

Steve Jones - Editor (5/15/2009)
This one?
http://www.sqlservercentral.com/Questions/Tags/System+databases

Thanks for the search tip, here it is:

http://www.sqlservercentral.com/links/158289/42671

QOD was posted May 8, 2008 - lots of replies 🙂

SMGarner Say Hey Kid Points: 668 More actions · Answer 3

[font="Arial"]While resource and mssqlsystemresource are both "resource" databases they are not the same if you referencing them in code for whatever reason - they are two distinct and different entities. Bad question.[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 4

Stuart,

My psychic powers allow me to detect that you answered incorrectly - as I did.

I am interested to know when you would reference it in code though?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 5

I have just install SQl 2008 and I do not see a resource database in system database? Do we need to install it?

Kindest Regards,

Amit Lohia

Noel McKinney Hall of Fame Points: 3452 More actions · Answer 6

Amit Lohia (5/15/2009)
I have just install SQl 2008 and I do not see a resource database in system database? Do we need to install it?

You don't need to install the Resource database. It's already present but "hidden" so that you won't see it in obvious places, like under system databases in SQL Server Management Studio's Object Explorer or by querying sys.databases. If you want to access it then you might enjoy the following piece by Kalen Delaney:

http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/13/geek-city-the-resource-database.aspx

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 7

FYI

I did run the select * from sys.databases and I got :

master

tempdb

model

msdb

reportserver$mssql2k8

reportserer$mssql2k8tempdb

and of course the other databases

..

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 8

more on this topic..

this is also what I got from running the few lines in below: 🙁

use mssqlsystemresource

Msg 911, Level 16, State 1, Line 7

Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly.

and..

SELECT id, name FROM master.sys.sysdbreg

Msg 208, Level 16, State 1, Line 2

Invalid object name 'master.sys.sysdbreg'.

Cheers,
John Esraelo

Noel McKinney Hall of Fame Points: 3452 More actions · Answer 9

John Esraelo (5/16/2009)
more on this topic..
this is also what I got from running the few lines in below: 🙁
use mssqlsystemresource
Msg 911, Level 16, State 1, Line 7
Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly.
and..
SELECT id, name FROM master.sys.sysdbreg
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.sysdbreg'.

The USE statement would require starting SQL Server in single user mode. The SELECT statement works when connected through the Dedicated Administrator Connection (DAC).

The SELECT queries in the last section of the following article don't require single user mode or DAC:

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

SanjayAttray SSChampion Points: 13157 More actions · Answer 10

Most amazing for me is looking at the results out of 953 attempts. If some one doesnot recognize ResourseDB as system database is Ok. May be they don't know but how can people have doubt about master, msdb, model and tempdb. They are part of SQL Server setup from ages ?

master 99%

msdb 97%

model 92%

tempdb 95%

SQL DBA.

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 11

It is amazing that they're all not 99%. I can see people mis-clicking things, so I'm not sure it will be 100%, but I'm surprised msdb isn't higher.

Noel McKinney Hall of Fame Points: 3452 More actions · Answer 12

I'm not surprised. I've interviewed too many SQL Server "experts" and "experienced DBAs" (according to the placement firm that sent them) who couldn't name any of the system databases. When that happened I would say "ok, well one of them is tempdb, so can you name the others" just to get things moving forward, but that usually wouldn't help.

jts2013 Hall of Fame Points: 3226 More actions · Answer 13

Thanks for this quesion, as I has never heard of / noticed the Resource database before, so I now know to include backing it up as part of any major upgrade. Cheers, John.:-)