Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jun 2007
    Posts
    29

    Unanswered: database data device turn to data & log device

    hi.

    i'm using sybase ASE 12.5
    sybase central 12.5.3

    i have little bit confious about my database device.i create 2 device.one for data and another one for log.after 2 years using it..i found the data device has becom "data and log" and the log device become "data".

    why it happen? and how to resolve it?
    what the problem if this become like this?

    if i make new database and new device then restore it.can their back to normal again?


    thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    How did it happen ? You loaded a database dump into it.

    Why did it happen ? You did not create the database exactly in the chronological create/alter sequence of the dumped database, as required.

    Problem ? The database is now not recoverable, so you have to fix it.

    How ? Find the exact sequence of create/alter database that was done, and create the new database using that sequence. Try "load database from dumpfile with listonly, headeronly", you can figure it out from that. If not, post again.

    If you do post again, post the result of the above command plus:
    Code:
    use mater
    go
    select * from sysusages order by lstart
    go
    Last edited by Derek Asirvadem; 09-02-09 at 01:35.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  3. #3
    Join Date
    Jun 2007
    Posts
    29
    thanks for replying..

    sounds like scary..my database now not recoverable.
    i'm sorry coz i'm very newbie in sybase and donno much about sybase. now i'm trying to learn part by part.

    how to find exactly sequence of create/alter my database?
    i just remember that i create database using default only.create 2 device for data and log.after that run script to create table.space for the database, i increased when there is an error about not have more space to database.

    database that have a problem is dbid=4

    this is result for query

    Code:
    1	7	0	3072	4	NULL	542	Nov 17 2005  2:18AM
    2	7	0	1536	4100	NULL	909	Nov 17 2005  2:18AM
    3	7	0	1024	3076	NULL	399	Nov 17 2005  2:18AM
    4	3	0	358400	67108866	NULL	749	Nov 17 2005  3:51PM
    5	3	0	358400	134217730	NULL	742	Mar  6 2006 11:59AM
    6	3	0	102400	33554434	NULL	94494	Nov 17 2005  9:29PM
    7	3	0	76800	134729730	NULL	70787	Nov 17 2005  3:01PM
    8	3	0	76800	134806530	NULL	34239	Nov 16 2006  5:05PM
    9	3	0	76800	100663298	NULL	2868	Nov 17 2005  3:04PM
    10	3	0	1024	251658242	NULL	415	Mar 21 2007 11:51AM
    11	3	0	153600	285212674	NULL	152403	May 21 2007  3:44PM
    12	3	0	153600	318767106	NULL	153600	Dec 30 2008 10:06PM
    13	3	0	15360	352321538	NULL	0	Feb  5 2008  2:05PM
    31513	7	0	1024	5636	NULL	367	Nov 17 2005  2:18AM
    31514	7	0	122880	16777218	NULL	83895	Nov 17 2005  2:18AM
    10	4	1024	1024	268435458	NULL	1020	Mar 21 2007 11:51AM
    2	7	1536	256000	167772162	NULL	255000	Nov 20 2005 12:47PM
    1	7	3072	7168	6660	NULL	6622	Nov 17 2005  2:18AM
    13	4	15360	5120	369098754	NULL	5100	Feb  5 2008  2:05PM
    13	4	20480	5120	369103874	NULL	5100	Feb 12 2008  9:46AM
    13	3	25600	35840	352336898	NULL	13978	Feb 13 2008  4:42PM
    13	4	61440	15360	369108994	NULL	15300	Feb 13 2008  4:42PM
    7	4	76800	25600	151250946	NULL	25500	Nov 17 2005  3:01PM
    8	4	76800	25600	134883330	NULL	25500	Nov 16 2006  5:05PM
    9	4	76800	51200	117440514	NULL	51000	Nov 17 2005  3:04PM
    6	4	102400	51200	50331650	NULL	51000	Nov 17 2005  9:29PM
    8	4	102400	117760	151276546	NULL	117300	Nov 16 2006  5:05PM
    9	4	128000	25600	117491714	NULL	25500	Feb  2 2006  3:41PM
    9	4	153600	25600	117517314	NULL	25500	Feb  2 2006  3:43PM
    11	4	153600	153600	301989890	NULL	152131	May 21 2007  3:44PM
    12	4	153600	153600	335544322	NULL	153600	Dec 30 2008 10:06PM
    9	4	179200	15360	117542914	NULL	15300	Jun  8 2006  9:52AM
    9	4	194560	10240	117558274	NULL	10200	Jul 30 2006  7:47PM
    9	4	204800	15360	117568514	NULL	15300	Jul 30 2006  8:11PM
    8	3	220160	25600	151394306	NULL	25500	Nov 16 2006  5:05PM
    9	3	220160	25600	100740098	NULL	22488	Aug 29 2006  3:11PM
    9	3	245760	25600	100765698	NULL	25500	Mar 20 2007 11:17AM
    2	7	257536	5120	13828	NULL	5100	Aug 29 2006 11:38AM
    2	7	262656	153600	168028162	NULL	153000	Aug 29 2006 11:38AM
    9	4	271360	10240	117583874	NULL	10200	Mar 20 2007 11:36AM
    9	3	281600	25600	100791298	NULL	25500	Mar 20 2007  1:36PM
    11	3	307200	25600	285366274	NULL	25500	Jun  3 2008 11:49AM
    12	3	307200	25600	318920706	NULL	25600	Dec 30 2008 10:06PM
    11	4	332800	51200	302143490	NULL	51000	Jun  9 2008 11:02AM
    12	4	332800	51200	318946306	NULL	51200	Dec 30 2008 10:06PM
    4	0	358400	102400	67467266	NULL	102000	Nov 17 2005  3:51PM
    5	0	358400	102400	134576130	NULL	102000	Mar  6 2006 11:59AM
    11	3	384000	51200	285391874	NULL	51000	Dec 23 2008  4:07PM
    12	3	384000	51200	335697922	NULL	51200	Dec 30 2008 10:06PM
    11	3	435200	10240	285443074	NULL	10200	Aug 20 2009 10:48AM
    11	4	445440	10240	302194690	NULL	10200	Aug 20 2009 10:48AM
    11	3	455680	5120	285453314	NULL	5100	Aug 30 2009  2:23PM
    4	4	460800	256000	67569666	NULL	255000	Nov 17 2005  3:51PM
    5	4	460800	51200	134678530	NULL	51000	Mar  6 2006 11:59AM
    5	4	512000	204800	150994946	NULL	204000	Mar  6 2006 11:59AM
    4	3	716800	51200	67825666	NULL	0	Nov 17 2005  3:51PM
    5	3	716800	51200	151199746	NULL	0	Mar  6 2006 11:59AM
    4	3	768000	153600	83886082	NULL	0	Nov 17 2005  3:51PM
    5	3	768000	256000	134908930	NULL	15209	Mar  6 2006 11:59AM
    4	3	921600	102400	84039682	NULL	238	Nov 17 2005  3:51PM
    4	3	1024000	10240	84142082	NULL	28	Aug  7 2006  1:52PM
    4	7	1034240	102400	67876866	NULL	112	Aug 29 2006  2:41PM
    4	7	1136640	51200	67979266	NULL	7	Oct  3 2006  2:54PM
    4	7	1187840	51200	68030466	NULL	56	Jan 25 2007 11:12AM
    4	7	1239040	102400	68081666	NULL	35	Jul 10 2007  3:03PM
    4	3	1341440	51200	84152322	NULL	77	Jul 10 2007  3:03PM
    4	7	1392640	25600	68184066	NULL	0	Sep 17 2007 11:29AM
    4	7	1418240	25600	68209666	NULL	677	Oct 28 2007 11:18AM
    4	7	1443840	25600	68235266	NULL	8	Jan  6 2008  3:51PM
    4	3	1469440	10240	84203522	NULL	0	Jan  6 2008  3:51PM
    4	7	1479680	25600	68260866	NULL	98	Aug 21 2008  4:37PM
    4	7	1505280	51200	68286466	NULL	48	Dec 23 2008  3:57PM
    4	7	1556480	51200	68337666	NULL	59	Mar 18 2009  2:12PM
    4	3	1607680	10240	84213762	NULL	48	Mar 18 2009  2:12PM
    4	7	1617920	25600	68388866	NULL	16	Mar 25 2009  2:44PM
    4	7	1643520	10240	68414466	NULL	0	Aug 13 2009 11:09AM
    4	3	1653760	10240	84224002	NULL	96	Aug 13 2009 11:09AM
    4	7	1664000	15360	68424706	NULL	15292	Aug 20 2009 10:47AM
    4	3	1679360	10240	84234242	NULL	10200	Aug 20 2009 10:47AM
    4	7	1689600	25600	68440066	NULL	25500	Aug 30 2009  2:22PM
    4	3	1715200	10240	84244482	NULL	10200	Aug 30 2009  2:22PM
    Last edited by rozihan; 09-02-09 at 05:13.

  4. #4
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    No problem being a newbie, but I will have to explain simple things. Don't worry about the tone or how to take my advice or what you know/do not know, just focus on the technical facts and the work. Your understanding of what you have done is not correct. There are no "tablespaces" in Sybase. Sure, you may have created a db with separate data/log (one each) allocations in the beginning, but every time you "added tablespace", you actually added a db allocation, there are now 27, with 14 mixed data/log and the original (first) log is unused.

    With that many mixed segments, we need to do two things:
    1 get the logsegment onto the correct device (and remove it from the data devices)
    2 (as per my first post) get the sequence of create/alter db (the "add tablespaces") right, so that you can dump/load without event.

    This will take some serious interaction, I will stay with you until the issue is closed. If you have a chat facility (YahooMessenger/SkypeChat/iChat) it will move faster. When it is complete, you can post the result of the exercise back here.

    Questions
    a I understand this is the db you want to correct, is this the test db (loaded into) or production db (dumped from) ?
    If it is the target (loaded into) db, then I need the output for sysusages from the source (dumped from) db.

    b Please provide sp_helpdevice output for the traget (loaded into) server

    c Which (one or two) devices is the intended or correct log device for dbid 4 ?
    • I understand how you got here, but it is not worth explainig, let's just fix it
    • hopefully you are keeping the data devices and the log devices separated for all dbs
    • You need to consider that the db is "working" right now with a huge log, unintended due to mixed data/log; therefore the initial 50mb may not be enough, etc
    • you currently have a db size of 3370mb
    • the log space available (not as you intended) is 1650mb
    • because it is mixed, the data space available is 3370mb
    • 821mb is unused
    • we want to end up with something like (eg) separated 3070mb data and 300mb log
    • or if you place the log on a new (not listed here) log device, then 3320mb data (the current size minus the one intended 50mb log) and 150mb log (the initial 50mb plus a new 100mb)
    • at the current rate of growth the 821mb unused wil get used soon enough
    • but only you will know
    Last edited by Derek Asirvadem; 09-02-09 at 10:32. Reason: Space usage detail added
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Besides the problem with dbid 4 which has a mixed data and a log segment (segmap 7) you also apear to use a data device for log and a log device for data. This can make recovery using log dumps imposable if you have a disk failure of one device

    Lets take the smaller dbid 8

    Here you have
    Code:
    dbid	segmap	lstart	size       vstart      data  log
    8	3	0	76800	134806530	150	
    8	4	76800	25600	134883330             50
    8	4	102400	117760	151276546	     230
    8	3	220160	25600	151394306	 50
    From the vstart values I see that the 1st log segment is on the same device as your data segment
    Lets assume the device names are db8_data and db8_log
    You incorrectly have 50 MB log on db8_data
    And at the end 50 MB data on db8_log

    to fix this, take a backup of the database
    drop the database and do the create database as follow
    create database db8
    on db8_data=150
    ,db8_log=280 -- segments following in sequence can be combined
    ,db8_data=50

    Load the database from backup and all should be OK

    Now for dbid 4 I suggest recreating the database and its devices
    Create a data device of 2470 MB and log of 500 MB
    Then
    create database db4
    on db4data=900
    ,db4log=500
    ,db4data=1970

    Now after the load drop all logsegments from db4data (sp_dropsegment)
    then use sp_extendsegment to extend the data onto the unused space

    If you now want to cleanup sysusages (show less lines) backup the db
    recreate it with above create database
    and after the load you should see 3 rows in sysusages for dbid 4
    data 900 MB on db4data
    log 500 MB on db4log
    data 1970 MB on db4data

  6. #6
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Rozihan

    For your consideration ...

    Keeping in mind the level of questioning, and your self-identification as a newbie, I did not explain everything, I answered as I did.

    I do not know if you are experienced enough, comfortable following the steps in pdreyer's instructions.

    I am not disagreeing with pdreyer's method, that is one method. Each method has its advantages and limitations. Based on your answer to my question "is this the test db or production db"; if this is production, then obviously I would not recommend dropping and recreating the db, or recreating the db and the devices; I use another method that will not disrupt the online availability of the database.

    Let us know what you want.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  7. #7
    Join Date
    Jun 2007
    Posts
    29
    hi again.
    thanks for your concern..i'm very appreciate it.

    my yahoo messenger id is : vearn21@yahoo.com


    hm..this is production db.that why i worried to much and scared to do anything.

    sp_helpdevice

    Code:
    tapedump1	/dev/rmt4                                     	disk, dump device	16	2	0	0	20000
    rmt0	/dev/rmt0.1                                   	tape,      20000 MB, dump device	16	3	0	0	640000
    rmt1	/dev/rmt1.1                                   	tape,      20000 MB, dump device	16	3	0	0	640000
    tapedump2	/dev/rst0                                     	tape,        625 MB, dump device	16	3	0	0	20000
    alldata_test	/sybdata1253/alldata_test.dev                 	special, dsync on, physical disk, 2000.00 MB	16386	0	8	134217730	135241729
    alllog_test	/sybdata1253/alllog_test.dev                  	special, dsync on, physical disk, 900.00 MB	16386	0	9	150994946	151455745
    awtlive_data	/sybdata1253/awtlive_data.dev                 	special, dsync on, physical disk, 150.00 MB	16386	0	21	352321538	352398337
    awtlive_log	/sybdata1253/awtlive_log.dev                  	special, dsync on, physical disk, 70.00 MB	16386	0	22	369098754	369134593
    cadenzadata	/sybdata1253/cadenzadata.dev                  	special, dsync on, physical disk, 300.00 MB	16386	0	2	33554434	33708033
    cadenzalog	/sybdata1253/cadenzalog.dev                   	special, dsync on, physical disk, 120.00 MB	16386	0	3	50331650	50393089
    codalivedata	/sybdata1253/codalivedata.dev                 	special, dsync on, physical disk, 2800.00 MB	16386	0	4	67108866	68542465
    codalivelog	/sybdata1253/codalivelog.dev                  	special, dsync on, physical disk, 840.12 MB	16386	0	5	83886082	84316223
    codatest1_db	/sybback1/dev/codatest1_db.dev                	special, dsync on, physical disk, 500.00 MB	16386	0	13	218103810	218359809
    codatest1_log	/sybback1/dev/codatest1_log.dev               	special, dsync on, physical disk, 500.00 MB	16386	0	12	201326594	201582593
    maplive_data	/sybdata1253/maplive_data                     	special, dsync on, physical disk, 491.91 MB	16386	0	17	285212674	285464533
    maplive_log	/sybdata1253/maplive_log.dev                  	special, dsync on, physical disk, 500.00 MB	16386	0	18	301989890	302245889
    maptest_data	/sybdata1253/maptest_data.dev                 	special, dsync on, physical disk, 450.00 MB	16386	0	19	318767106	318997505
    maptest_log	/sybdata1253/maptest_log.dev                  	special, dsync on, physical disk, 400.00 MB	16386	0	20	335544322	335749121
    resitlivedata	/sybdata1253/resitlivedata.dev                	special, dsync on, physical disk, 300.00 MB	16386	0	6	100663298	100816897
    resitlivelog	/sybdata1253/resitlivelog.dev                 	special, dsync on, physical disk, 300.00 MB	16386	0	7	117440514	117594113
    segment1	/sybdata/segment1.dev                         	special, dsync on, physical disk, 300.00 MB	16386	0	14	234881026	235034625
    sysprocsdev	/usr/sybase1253/data/sysprocs.dat             	special, dsync on, physical disk, 240.00 MB	16386	0	1	16777218	16900097
    tempdbdata	/sybdata1253/tempdbdata.dev                   	special, dsync on, physical disk, 1000.00 MB	16386	0	10	167772162	168284161
    zihandata	/sybback1/zihandata.dev                       	special, dsync on, physical disk, 5.00 MB	16386	0	15	251658242	251660801
    zihanlog	/sybback1/zihanlog                            	special, dsync on, physical disk, 5.00 MB	16386	0	16	268435458	268438017
    master	/usr/sybase1253/data/master.dat               	special, dsync on, default disk, physical disk, 60.00 MB	3	0	0	2	30721
    the correct log device for dbid 4 is

    Code:
    codalivelog	/sybdata1253/codalivelog.dev                  	special, dsync on, physical disk, 840.12 MB	16386	0	5	83886082	84316223
    data device is

    Code:
    codalivedata	/sybdata1253/codalivedata.dev                 	special, dsync on, physical disk, 2800.00 MB	16386	0	4	67108866	68542465

  8. #8
    Join Date
    Jun 2007
    Posts
    29
    pdreyer / derek :

    i donno which method should i choose because as i said before i'm very newbie.i cant make my own decision.this is production database so maybe u guys can help me to choose what is the best method.

    is impossible to me to drop the database because this is production db.db use every day except friday and saturday.so i have only 2 days to do anything.


    tq.

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I suggest you load into a dev environment to play with until you are comfortable with the steps to follow.
    Ensure you create the fragments in the same way as live i.e.
    disk init name=codadevdata,size='2600m'...
    disk init name=codadevlog,size='770m'...
    create database codadev on
    codadevdata=700
    ,codadevdata=200
    ,codadevdata=500
    ,codadevdata=100
    ,codadevlog=300
    ,codadevlog=200
    ,codadevlog=20
    ,codadevdata=200
    ,codadevdata=100
    ,codadevdata=100
    ,codadevdata=200
    ,codadevlog=100
    ,codadevdata=50
    ,codadevdata=50
    ,codadevdata=50
    ,codadevlog=20
    ,codadevdata=50
    ,codadevdata=100
    ,codadevdata=100
    ,codadevlog=20
    ,codadevdata=50
    ,codadevdata=20
    ,codadevlog=20
    ,codadevdata=30
    ,codadevlog=20
    ,codadevdata=50
    ,codadevlog=20
    for load

  10. #10
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    If it is production, and you are a newbie, ie. not totally comfortable with the business of dropping and created databases and devices; and restoring them; and handling any issues that come up, I would advise against pdreyer's method. Two days will not help, in the sense that if you get stuck, and something goes wrong, it may well be a lot more than two days to get someone to fix it. As you can see, it is prone to typos. As stated, nothing wrong with that method, I have used it myself many times, it is quick and complete, but it requires understanding of the identified tasks, and ability to fix problems if they happen.

    The Production Method

    Read up on it, so you understand the steps. This method is safe (no accidental damage possible) and supported. Test the steps on your test server, then execute once in production on Thurs night or Fri morning. I've just tested it on a live 15.0.2 system, unfortunately I a 12.5.4 system is not handy.

    1 Kill any connections that are still open on codalive:
    Code:
    sp_who  -- look for codalive
    go
    kill <spid> 
    ...
    go
    2 Dump (truncate) the transaction log, to minimise the active portion of the log
    Code:
    dump tran codalive to <log_dump_file_1>
    go
    3 Dump the database, to be safe, to have a full recovery point
    Code:
    dump database codalive to <db_dump_file_1>
    4 Set codalive to single user mode. Remain in master, and exec steps from there:
    Code:
    use master
    go
    exec sp_dboption codalive, single, true  
    go
    
    -- if people are still connected, this will fail; repeat (1) and return to (4)
    
    checkpoint
    go
    
    -- this is the critical step
    
    exec codalive..sp_logdevice codalive, codalivelog
    go
    5 Step (4) looks after the "future allocation" of the log. The log is actually still where it was. Truncate the log, to minimise it and move it (if we are lucky).
    Code:
    dump tran codalive to <log_dump_file_2>
    go
    exec sp_helplog codalive  -- tell us where log starts
    go
    This will indicate where the log STARTS; if it states "codalivelog", we are complete, goto (7). If not, there are still a few log pages on codalivedata, so we have to ...

    6 Get the log onto the new log device. We just need to do a bit of logging. Do something like the following, you will know table names, etc.
    Code:
    use codalive
    go
    set rowcount 500
    select * into JUNK from <some_table>
    go
    
    -- ensure you did get "500 rows affected"
    
    set rowcount 0
    delete junk
    go
    
    -- RETURN TO STEP (5)

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

    7 Dump the database, in its new fixed state, this is the real production full recovery point. Clear single user mode
    Code:
    dump database codalive to <db_dump_file_1>
    go
    
    use master
    go
    exec sp_dboption codalive, single, false  
    go
    checkpoint
    go

    Ask questions, get your head around it. Try the entire sequence (every step) on test server or a test database, make sure you understand it, try to fix any issues that come up, but ask any questions as well, and get fully prepared for Thu/Fri night.


    Next week we can discuss how to ensure it does not happen again.
    Last edited by Derek Asirvadem; 09-03-09 at 11:17.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Look out for data left stranded on the log device when using Derek's method

  12. #12
    Join Date
    Jun 2007
    Posts
    29
    ok guys..i trying to follow derek method.if failed then we try using pdreyer method.

    i'm stuck in step 2. the error is

    Code:
    1> dump tran codalive to '/sybback1/codalivetran04092009'
    2> go
    Msg 4205, Level 16, State 1:
    Server 'SYBASEKKBENDAHARI1253', Line 1:
    Syslogs does not exist in its own segment in database 'codalive' with segmap '7'
    with logical start page number of '1034240'. You cannot use DUMP TRANSACTION in
    this case, use DUMP DATABASE instead.
    1>

  13. #13
    Join Date
    Jun 2007
    Posts
    29
    i think its difficult for me to understand pdreyer method because i'm just knowing little in command isql.i using sybase central to make a database.that's my mistake.always wanna do an easy things because i don have proper training to work with sybase.so maybe its time for me to learn from the basic.please guide me.


    FYI, time now at my place is 3am friday morning.i'm work from 8am to 5pm.maybe can extend to 11am.here is fasting month now.maybe is difficult for us to work together because of different daytime.so i will try then i post here what i get.

  14. #14
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Rozihan
    i'm just knowing little in command isql
    I understood that, that why I have given you a method that works at your level of understanding/capability. After this lot is closed, we will check, and make sure that no data is left "stranded" in the log. No use complicating the current piece of work with that problem, we can handle it if and when it comes up. One step at a time.

    I think you have to go with one XOR the other method, until it is complete. You cannot afford to stop and reverse or switch to the next method (which you have previously decided against) just because you have a problem. No, stay and resolve the problem, and progress, step by step. This is normal for progressing any support issue.

    Error
    Ok, so that means you have NEVER dumped the tran log (most production databases have this as a scheduled event). You have been truncating the log all this time. Probably even have sp_dboption 'truncate_log_on_checkpoint' set. That means that your database has NEVER been recoverable (except for going back to a full_db_dump). Not something that I expected for a production db.

    Solution
    Skip step (2).
    Continue with step (3).

    Long Term Requirement
    Treat it like it is a production database, the recoverability is important, reset your options, schedule a task to dump the log.
    Last edited by Derek Asirvadem; 09-04-09 at 07:55.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  15. #15
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You won't be able to do transaction log backups while you have mixed data and log. Do a full database backup.

    Quote Originally Posted by rozihan
    its difficult for me to understand pdreyer method
    Which part of what I've described before do you not understand?
    You have done backups and restores before haven't you?
    I'll try and explain again

    After the successful backup drop the database

    If you want you can use Sybase central to delete and create the devices with the sizes I suggested (codalivedata of 2470 MB and codalivelog of 500 MB). But you don't have to as it is already large enough. There will be free space left on the device if you leave them as is.

    To ensure the database fragments is created as expected I suggest you use isql or sqladvantage to execute the create database
    Code:
    create database codalive on
    codalivedata=900
    ,codalivelog=500
    ,codalivedata=1970 
    for load
    Now restore (load) the database and then drop the logsegment from the data device
    You can use Sybase Central
    Databases>codalive >segments>logsegment>
    properties>devices
    click on the codalivedata device and remove

    To verify the result
    Code:
    select u.*, d.name
    from sysusages u
    join sysdevices d
      on u.vstart between d.low and d.high
     and d.status&2=2
    where dbid=db_id('codalive')
    order by u.lstart
    segmap
    0 unused
    1 system
    2 default (data)
    3 default(data) & system
    4 log
    7 mixed data & log
    >7 other user defined segments
    Last edited by pdreyer; 09-04-09 at 09:12.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •