Solved

How to move existing transaction log to another disk

Posted on 2007-11-14
3
788 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
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sybase ASE 15.7 do not know datepart function 2 1,203
How do I get SCOPE_IDENTITY working correctly. 3 829
SQL Query Syntax 5 170
SyBase Query Syntax Case When 7 119
What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now