Post reply

resource database - move or not?

  • September 11, 2008 at 1:57 pm

    #193474

    quick question..

    i read somewhere today (i forget where, but i made a note of it) that the resource database mssqlsystemresource needs to physically in the same place as the master.mdf, otherwise theres some odd behaviour, but i also read that microsoft does not advise moving it..

    can i have my master.mdf somewhere different to the mssqlsystemresource.mdf or does it cause issues?

    thanks

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • September 11, 2008 at 2:31 pm

    #870638

    I've read that it causes issues. BOL has data on how to move system databases.

    I haven't tried moving master or resources, I've just read about it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 11, 2008 at 2:39 pm

    #870644

    from what I remeber from posts on this site if the resources database is not in the same location as the master database SQL upgrade can (will?) fail. The two are closely linked and I would never advise seperating them, why would you?

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

  • September 11, 2008 at 2:54 pm

    #870652

    i moved my master database.. the only reason i wouldn't move the resource database is because its a damn pain to move

    that being said, i did get it altered by starting in /c /f /T3608

    from the post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=126761&SiteID=1

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • September 11, 2008 at 3:05 pm

    #870658

    ok so i managed to move the mssqlsystemresource database..

    now the only one left in this drive is distmdl

    i've read what its for.. but not sure how i can move it as if i try to 'use' it, even in single user mode, it tells me it doesn't exist

    can someone tell me how to move distmdl please?

    thanks

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • September 11, 2008 at 3:38 pm

    #870678

    theres another current post on this subject:

    http://www.sqlservercentral.com/Forums/Topic220317-146-1.aspx

    this technet article is the one I would believe on moving 2005 system databases. it says master and resources must be in same location

    http://technet.microsoft.com/en-gb/library/ms345408(SQL.90).aspx

    In my search I keep getting pointed to the technet article on moving 2008 databases, that seems to suggest when you apply an upgrade , the resource database will be moved back to its default location. Its not clear if that happens in 2005.

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

  • September 12, 2008 at 6:14 am

    #870889

    mssqlsystemresource gets replaced each time you apply a Service Pack, problem is Service Pack "thinks" mssqlsystemresource is located in the same place Master database is so... if you move mssqlsystemresource.mdf and you do not move mssqlsystemresource.ldf at the time you apply a Service Pack you are gonna end with a mix of old and new mssqlsystemresource datafiles resulting in the inability to start SQLServer.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 12, 2008 at 7:07 am

    #870919

    i read something about that.. which is why i broke my own rule and put the .ldf in the same drive/directory as the .mdf

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • September 12, 2008 at 7:33 am

    #870948

    apart from tempdb potentially I have always thought separating data and log for system dbs WAY over the top..............

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

  • September 12, 2008 at 7:44 am

    #870963

    george, over the top or not.. thats the standard we have, so I'm not about to change it. Personally, I feel better having them on seperate drives.

    anyway... I'm still stuck on moving distmdl

    Heres what I did ( I figured it'd be similar to mssqlsystemresource)

    NET STOP MSSQLSERVER

    NET START MSSQLSERVER /c /f /T3608

    H:\>sqlcmd

    1> alter database distmdl modify file (name=data, filename = 'E:\MSSQL Data\dist

    mdl.mdf')

    2> go

    Msg 911, Level 16, State 1, Server SCWRPT01, Line 1

    Could not locate entry in sysdatabases for database 'distmdl'. No entry found wi

    th that name. Make sure that the name is entered correctly.

    I also tried:

    1> sp_detach_db 'distmdl'

    2> go

    Msg 15010, Level 16, State 1, Server SCWRPT01, Procedure sp_detach_db, Line 34

    The database 'distmdl' does not exist. Use sp_helpdb to show available databases.

    I'm stumped on how to move distmdl.. hope someone can help

    Thanks!

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • September 12, 2008 at 7:56 am

    #870977

    SQL is not using distmdl files right now so just use explorer to move them to the new location. Not sure whether SQL will need to be down or not, try it.

    Not sure why you are more comfortable with systemdb data and log files in different drives, its not buying you anything and is causing you problems, and will bite you when you do an upgrade. sounds like time to change that standard , it should only apply to application databases..

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

  • September 12, 2008 at 8:14 am

    #870997

    i guess sometimes the simplest solution is the one that works.. i moved the distmdl files while it was down, and i restarted just fine.

    I understand your point of not moving the system databases around (with the exception of tempdb) -- they dont really change enough to cause any major need to be in different drives.. the standard was in place when i got here, and i was just moving along with it..

    but like i say.. you raise a good point, and i'll definitely bear it in mind.. as a service pack will probably cause me some issues with the resource database.

    thanks for your help guys

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • September 12, 2008 at 8:48 am

    #871040

    torpkev (9/12/2008)

    as a service pack will probably cause me some issues with the resource database.

    You can take out the "probably" from your phrase, when the Service Pack day comes you will be unable to start SQL Server services.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 12, 2008 at 8:54 am

    #871049

    PaulB (9/12/2008)

    torpkev (9/12/2008)

    as a service pack will probably cause me some issues with the resource database.

    You can take out the "probably" from your phrase, when the Service Pack day comes you will be unable to start SQL Server services.

    You guys raise some good points on this.. I'll probably keep master, msdb, model and the resource database in the default location and change the standard. Last thing I'll want to deal with on service pack day is dicking around with resource databases.

    That being said, its nice to atleast know how to do it if I have to.

    Thanks for all your help guys

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

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • September 12, 2008 at 9:28 am

    #871081

    default location is C:\program files which is not good location for system databases for anything other than a PC installation.

    define a directory on a drive other than C drive and away from your app databases if poss, put all systemdb files together in there and set that as your standard.

    Move and resize tempdb post install if you have to.

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

Viewing 15 posts - 1 through 15 (of 19 total)

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