Attribute to identify system databases

Question

Attribute to identify system databases

Viewing 12 posts - 16 through 26 (of 26 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 1

I don't know that a table driven approach is being dismissed, and I think that's the best idea. One I'd recommend over Paul's. Another system db is possible, and if MS listens, I hope we get a tempdb for each db, which could result in all sorts of issues.

Stick static names in a table, reference this for your maintenance, send a script if the names ever change.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Since many DBA's create an "admin" database to house such things as numbers tables, stored procs, and other administrative types of objects - it is perfectly plausible to house a table in that database that is updated with the names of the system databases. Properly designed it could avoid the potential problems illustrated by Steve (tempdb for each database).

I think the question illustrates pitfalls with any proposed solution - should MS decide to change the database names.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 3

Steve Jones - Editor (3/13/2010)
... and if MS listens, I hope we get a tempdb for each db

Lordy, I hope that doesn't happen. Imagine trying to correctly size all of those.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Steve Jones - Editor (3/13/2010)
I don't know that a table driven approach is being dismissed

Not by me it wasn't - either approach will work.

I see no compelling difference between centralizing a list in a table, or in a table-valued function.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Jeff Moden (3/13/2010)
Steve Jones - Editor (3/13/2010)
... and if MS listens, I hope we get a tempdb for each db
Lordy, I hope that doesn't happen. Imagine trying to correctly size all of those.

It's called job security :-D;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 6

CirquedeSQLeil (3/13/2010)
Jeff Moden (3/13/2010)
Steve Jones - Editor (3/13/2010)
... and if MS listens, I hope we get a tempdb for each db
Lordy, I hope that doesn't happen. Imagine trying to correctly size all of those.
It's called job security :-D;-)

Could be "job insecurity". It's one more thing they can blame you for when the whole bloody world misuses the latest/greatest flash-in-the-pan as they always do. I will admit that there will be a whole lot more posts concerning poor performance and insane disk usage, though, and those could be fun. 😀

Although I can see some performance advantage by placing each TempDB for each database on separate spindles, I can see some "disk bloat" occuring because of the non-concurrent use of disk space that would cause. I hope they at least give the option to use a more traditional TempDB instead of forcing this new idea upon us all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

If Microsoft were to add a tempdb for each database instead of a single global tempdb, wouldn't it make sense to implement it as a separate filegroup that we would have to maintain and manage just like we would user (DBA) created filegroups? I could see one for the database and one for the log file.

Not saying I am completely for such a change, but I could see it implemented in that manor.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

I see this as being done for 1 of two reasons. 1. It would improve performance. 2. It is a marketing effort to befriend more average joe user's and try to make them into DBA's. My gut says it is truly the second of those reasons. That is why CRM has been written with an end-user in mind and why office has those stupid ribbons.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 9

Lynn Pettis (3/14/2010)
If Microsoft were to add a tempdb for each database instead of a single global tempdb, wouldn't it make sense to implement it as a separate filegroup that we would have to maintain and manage just like we would user (DBA) created filegroups? I could see one for the database and one for the log file.
Not saying I am completely for such a change, but I could see it implemented in that manor.

Sure... but that's what I was talking about... possible performance increase at the cost of disk bloat.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Without knowing the details, my gut-feel about multiple-tempdbs is all bad.

Many shops struggle to get tempdb genuinely on its own drive/controller/bus (think SANs).

I find it hard to see how making the situation more complex can possibly help.

MSFT do not have a great track record with version 1.0 features.

Scares me silly.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sentitus Grasshopper Points: 19 More actions · Answer 11

To cut this topic...

I was thinking about how "Microsoft" identifies its own system databases, maybe there are some

hidden flags, but how to find them and how do they do that?

As you all know within SSMS when you expand certain folder subtrees, a specific T-SQL code

is generated to represent various objects. You are able to see this commands when you (of course)

run SQL Profiler.

And this is the WHERE part of the t-sql code when you expand "Databases\System Databases" :

.

WHERE

(CAST(case when dtb.name in (''master'',''model'',''msdb'',''tempdb'') then 1 else dtb.is_distributor end AS bit)=@_msparam_0 and.......

.

:))))))

greetings

D.Buzuk

Croatia

zionassedo Newbie Points: 2 More actions · Answer 12

Well, coding a table maintenance job for 140 different servers which are from 2005 to 2019 version, with a reporting server or not, with sdt or not, it is very useful to know that you will not fail trying to rebuild indexes on system tables.