Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    25

    Question Unanswered: Moving transaction log for master/model/msdb databases

    How do you do this out of interest. I know that when you create a new db you can specify a path for the transaction log i.e a searate physical disk.

    Question is how do I put the transaction logs for the system databases on another disc - I can't remember seeing an option during installation....


    Thanks

    FunkyD

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    why would you want to move these files? These db see little activity.

    If you were posting a general question, How do I move a Database to a new location? then check out the following:

    http://support.microsoft.com/default...b;en-us;224071
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2003
    Posts
    25
    I guess if they have little activity it's not necessary I suppose....

    If you can't backup the system databases after a change then would the transaction logs help you with recovery?

    We have a lot of users creating/deleting databases and I figured that the T-logs might come in handy and so thought it wise to give them the same protection as a normal db.....

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What prevents you from backing up the system DB after a change?

    Granted you might want to backup a user's db but the entry in the master db will ge tcreated when you restore the backup.

    IMHO backing up the master db after changes saves you on lost logins and recovering from a bigger dissaster.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Mar 2003
    Posts
    25
    How about that I don't know when changes are made lol....

    That's another issue but one I am trying to work on. There is no way people should be doing stuff ad hoc on a production system... but they do!!

    Thanks for the advice :-)

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You can always make friends by revoking sa from these people and force them to justify getting it back!

    Again, just restoring the master DB would do little to help in restoring from a lost DB, so as long as you make a backup after changes you should be good to go. Also this might be a good reason for users to NOT be doing adhoc stuff on the production box.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Moving transaction log for master/model/msdb databases

    FunkyD,

    The four system databases each have different methods for moving the physical files (including the log files).

    Master:
    1. Change the startup parameters (from the properties page for the server).
    2. Shut down SQL Server.
    3. Move the physical files (using Explorer)
    4. Restart SQL Server (crossing your fingers here might help)

    Model:
    This involves setting a trace flag somewhere; look it up on line (use Google) but it is doable.

    MSDB:
    Same, it involves setting a trace flag. Look it up on Google.

    TempDB:
    This one is easy:
    1. User ALTER DATABASE and the options to specify physical file locatations.
    2. Restart SQL Server
    3. The new TempDB master and log files will be created automatically; you can remove the old files by hand later.

    As for why you might want to do this, I can't really think of any good reasons to move master or model. TempDB, on the other hand, can get a lot of use and it might help performance to move it to a separate partition that otherwise does not have a lot of I/O.

    HTH,

    Hugh Scott

    Originally posted by FunkyD
    How do you do this out of interest. I know that when you create a new db you can specify a path for the transaction log i.e a searate physical disk.

    Question is how do I put the transaction logs for the system databases on another disc - I can't remember seeing an option during installation....


    Thanks

    FunkyD

  8. #8
    Join Date
    Mar 2003
    Posts
    25
    Thanks - okay, I won't bother moving these dataabases but instead will look at getting a procedure (another one!!) to ensure updates get a backup afterwards..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •