Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Smile Unanswered: delete/move transaction log

    Hi all,

    I'm an old Oracle dba that had to convert DB religion :-)

    Quick question for you gurus.

    If i have a disk that is starting to break down, but still working, and that disk has the Transaction log for the SQL2000 DB, is there an easy, quick way to move the transaction log to a different disk while we repair the disk ? As little downtime as possible is what i'm looking for.

    Or do i have to ....
    1. Full DB backup
    2. Fix the disk
    3. Full DB restore since the transactionlog is missing.

    I have looked thru the SQL200 Admin Companion but i really miss i small whitepaper giving some general procedures what to do if you loose transction log disk, datafile disk etc etc.

    Anyone have any good whitepapers or know where it can be downloaded ?

    Hope this question isn't to trivial for you experts or that it has been asked before, i did search first.

    Thanks.

  2. #2
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Re: delete/move transaction log

    Hey there,

    What you should be able to do is:

    1. Shutdown applications and users accessing the database.
    2. Detach the database and transaction log.
    3. Copy the transaction log file(s) (.LDF) to your new drive.
    4. ReAttach DB and log (with new location)
    5. Allow users etc back in & fix disk.
    6. Once disk fixed repeat process.

    The longest part will be the file copy.. There is information in Books Online to help you. You can either use Enterprise manager or T-SQL.

    Hope this helps.

  3. #3
    Join Date
    Jul 2003
    Posts
    2

    Smile Thanks

    Thanks mate, sounds like good way to do it.

    I'll have a look at the books online once more.


    Cheers

  4. #4
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Re: Thanks

    No worries,

    The stored procs you will require should you choose T-SQL are: sp_attach_db & sp_detach_db. Search for info on these and you should find some info to help you..

    Cheers

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This can also be done with no downtime....provided you have diskspace ;-).

    1) Create a brand new transaction log on a good disk.
    2) Run DBCC SHRINKFILE ((Old logfile id), EMPTYFILE)
    This last command marks the old logfile as "not to be used anymore"
    3) Backup any transactions that may be in the bad file.
    4) Drop the bad logfile.

    Good luck.

Posting Permissions

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