Solved

How to move existing transaction log to another disk

Posted on 2007-11-14
3
786 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Taking long time 7 466
Sybase - sp_cacheconfig 1 946
Sybase - How to edit cron schedule for sybase backups 3 833
Crystal Reports VB6 11 1,020
What is Backup? Backup software creates one or more copies of the data on your digital devices in case your original data is lost or damaged. Different backup solutions protect different kinds of data and different combinations of devices. For e…
Find out what the Office 365 disclaimer function is, why you would use it and its limited ability to create Office 365 signatures.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

16 Experts available now in Live!

Get 1:1 Help Now