Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to move existing transaction log to another disk

Posted on 2007-11-14
3
Medium Priority
?
796 Views
Last Modified: 2012-06-22
My transaction log is currently on the same disk as my database, and I want to move it too another. I understand the steps involved, but don't get the syntax right:
1. use disk init to create a device on another disk
2. use alter database to allocate the device to the database
3. use sp_logdevice to specify the new device for logging
From the sysadmin guide I see that that's what to do. I must name the new device differently from the original; so it should be logdev02 rather than logdev01. Do I use sp_dbextend? In the original installation script it says, sp_dbextend 'set', 'database', mydbs, 'logdev01', '1024M'

All help gratefully accepted.
/Cap
0
Comment
Question by:JadeBear
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 20288048
I know that's what the manual says, and I find it more complex than it needs to be.

Note that the method you're using doesn't instantly release the old area - all existing transaction logs remain there until removed by some form of DUMP TRAN method (or "trunc. log on chkpt" option). You also can't remove the old device fragment from the database... so I don't think this is what you want.

I have a much easier (but slower) method for you that is also fully supported by Sybase: Sybase disk mirroring:

1) disk mirror name="current_log_device_name", mirror="path_and_name_of_device_on_new_disk"
2) disk unmirror name="log_device_name", side="primary", mode="remove"

This says mirror the device from the old location to the new, and then break the mirror leaving only the new. The old device is immediately unused and can be deleted at once. You don't have to increase the overall database size either.

Note you have to enable "disk mirroring" (needs a Sybase restart), and the first step of creating the mirror is very very slow, and suspends all activity that would write to the device (for a log device, that is all writes). But it always works and is conceptually very clean.
0
 

Author Comment

by:JadeBear
ID: 20288808
Thank you! I was hoping that something like that was possible (since that's what I've been using on other devices) but the SAG didn't mention anything so simple.

Regards,
Cap
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 20302554
Yes, it's odd given this is a fully supported and documented solution. It's even written up in the SAG, just not as a solution for moving the log... but a device is a device, it works for logs too.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Want to know how to use Exchange Server Eseutil command? Go through this article as it gives you the know-how.
Experts give $3,000 toward hurricane relief efforts—but that's not all!
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question