Attribute to identify system databases

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719974

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

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

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • SQLRNNR

    SSC Guru

    Points: 281243

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

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Lynn Pettis

    SSC Guru

    Points: 442337

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

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

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Paul White

    SSC Guru

    Points: 150442

    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.

  • sentitus

    Grasshopper

    Points: 19

    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

    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.

     

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

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