Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Moving the Log to a New Device

Hi Community.

I have a database created with data and log on the same device, I need move the log to a new device. For This I Used the method with sp_logdevice and the result is succesfully.

I'm  validating this, with sp_helplog, sp_helpdb and I can see the new organization of the segments:

1> sp_helpdb mixed_LD

2> go

name     db_size       owner dbid created      durability lobcomplvl inrowlen status

-------- ------------- ----- ---- ------------ ---------- ---------- -------- ------------------

mixed_LD       60.0 MB sa       7 Feb 11, 2015 full                0     NULL mixed log and data

(1 row affected)

device_fragments               size          usage                created                   free kbytes

------------------------------ ------------- -------------------- ------------------------- ----------------

prueba_mixed                         50.0 MB data only            Feb 11 2015  3:31PM                  49232

prueba_mixed_log                     10.0 MB log only             Feb 11 2015  3:32PM       not applicable


log only free kbytes = 10170

device           segment

---------------- ----------

prueba_mixed     default

prueba_mixed     system

prueba_mixed_log logsegment

But I have a big question: Why if the process is sucessfully  and the logsegment now is in your own device, the status of database remains in "mixed log and data"?


Cristian Lopez.


The sp_logdevice procedure does not move any existing extent allocations belonging to syslogs, it just updates the device-segment mapping to control where future allocations of the log can go.  The existing log extents allocations on the old device need to be deallocated (truncated) away before the database is eligible to be considered to have separate data and log (which allows full transaction log dumps to be taken), and ASE has to verify that that the database doesn't have any data extents on the log segment or log segments on non-log segments before changing the status bit that indicates this state.

The full steps for separating data and log are here in the documentation, you still need to do steps 2-6.  Moving the Transaction Log to Another Device.



0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question