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.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.|||Thanks mate, sounds like good way to do it.

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

Cheers|||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|||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.