Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    41

    Unanswered: logsegment question

    Hi,

    I installed a new 15.0.3 ASE on one of our LPARs. After installation I created a database called 'dem'. I loaded the dump from the 12.5.0.3 production database into it with the:
    load database dem from /bkup/dump/surdump1'
    command. Although I created the dem database by first creating it in a data device and on two log devices and increased it after the creation with adding one by one the needed devices to it, after the load the segments were mixxed up, having the start of the log on the 5th data device, etc.. I moved the log and dumped the database and the log, but interestingly the logsegment still indicate that 1026MB is used.

    Here is the sp_helpsegment logsegment output:

    1> use dem
    2> go
    1> sp_helpsegment logsegment
    2> go
    segment name status
    ------- ---------- ------
    2 logsegment 0
    device size
    ------- -------
    surlog1 512.0MB
    surlog2 512.0MB
    surlog3 512.0MB
    free_pages
    -----------
    261079

    Objects on segment 'logsegment':

    table_name index_name indid partition_name
    ---------- ---------- ----- --------------
    syslogs syslogs 0 syslogs_8

    Objects currently bound to segment 'logsegment':

    table_name index_name indid
    ---------- ---------- -----
    syslogs syslogs 0

    total_size total_pages free_pages used_pages reserved_pages
    ----------------- --------------- --------------- --------------- ---------------
    1536.0MB 786432 261079 3159 0
    (return status = 0)

    Here is the sp_helpdb dem output:

    1> sp_helpdb dem
    2> go
    name db_size owner dbid created status
    ---- ------------- ----- ---- ------------ ------------------
    dem 67072.0 MB sa 4 Sep 23, 2010 mixed log and data

    (1 row affected)
    device_fragments size usage created free kbytes
    ------------------------------ ------------- -------------------- ------------------------- ----------------
    surdev1 8192.0 MB data only Oct 4 2010 3:14PM 4632
    surlog1 512.0 MB log only Oct 4 2010 3:14PM not applicable
    surlog2 512.0 MB log only Oct 4 2010 3:14PM not applicable
    surdev2 8192.0 MB data only Oct 4 2010 3:14PM 926
    surdev3 8192.0 MB data only Oct 4 2010 3:14PM 70724
    surdev4 8192.0 MB data only Oct 4 2010 3:14PM 51374
    surdev5 5120.0 MB data only Oct 4 2010 3:14PM 43742
    surdev5 1024.0 MB data only Oct 4 2010 3:14PM 1044480
    surdev5 2048.0 MB data only Oct 4 2010 3:14PM 13210
    surdev6 8192.0 MB data only Oct 4 2010 3:14PM 15070
    surdev7 8192.0 MB data only Oct 4 2010 3:14PM 7136
    surdev8 8192.0 MB data only Oct 4 2010 3:14PM 6193366
    surlog3 512.0 MB log only Oct 4 2010 5:32PM not applicable

    --------------------------------------------------------------
    log only free kbytes = 522158
    device segment
    ------- ----------
    surdev1 default
    surdev1 system
    surdev2 default
    surdev2 system
    surdev3 default
    surdev3 system
    surdev4 default
    surdev4 system
    surdev5 default
    surdev5 system
    surdev6 default
    surdev6 system
    surdev7 default
    surdev7 system
    surdev8 default
    surdev8 system
    surlog1 logsegment
    surlog2 logsegment
    surlog3 logsegment
    (return status = 0)


    Here is the output of a select from the sysusages table:

    1> use master
    2> go
    1> select * from sysusages
    2> go
    dbid segmap lstart size vstart pad unreservedpgs crdate vdevno
    ------ ----------- ----------- ----------- ----------- ------ ------------- -------------------------- -----------
    1 7 0 6656 4 NULL 2759 Sep 21 2010 1:08PM 0
    3 7 0 1536 6660 NULL 654 Sep 21 2010 1:08PM 0
    2 0 0 2048 8196 NULL 1188 Sep 21 2010 1:08PM 0
    31513 7 0 1536 10244 NULL 591 Sep 21 2010 1:08PM 0
    31514 7 0 102400 2 NULL 42028 Sep 21 2010 1:09PM 1
    1 7 6656 26112 11780 NULL 25874 Sep 21 2010 1:09PM 0
    31513 7 1536 1536 2 NULL 1530 Sep 21 2010 1:12PM 2
    2 3 2048 524288 2 NULL 522240 Sep 21 2010 1:12PM 3
    2 3 526336 524288 2 NULL 522240 Sep 23 2010 11:49AM 4
    2 4 1050624 524288 2 NULL 522240 Sep 23 2010 11:50AM 5
    4 4 34078720 262144 2 NULL 261104 Oct 4 2010 5:32PM 16
    4 3 0 4194304 2 NULL 214 Oct 4 2010 3:14PM 6
    4 4 4194304 262144 2 NULL 0 Oct 4 2010 3:14PM 7
    4 4 4456448 262144 2 NULL 0 Oct 4 2010 3:14PM 8
    4 3 4718592 4194304 2 NULL 0 Oct 4 2010 3:14PM 9
    4 3 8912896 4194304 2 NULL 35408 Oct 4 2010 3:14PM 10
    4 3 13107200 4194304 2 NULL 25575 Oct 4 2010 3:14PM 11
    4 3 17301504 2621440 2 NULL 21617 Oct 4 2010 3:14PM 12
    4 3 19922944 524288 2621442 NULL 522240 Oct 4 2010 3:14PM 12
    4 3 20447232 1048576 3145730 NULL 6246 Oct 4 2010 3:14PM 12
    4 3 21495808 4194304 2 NULL 5316 Oct 4 2010 3:14PM 13
    4 3 25690112 4194304 2 NULL 2707 Oct 4 2010 3:14PM 14
    4 3 29884416 4194304 2 NULL 3097588 Oct 4 2010 3:14PM 15

    (23 rows affected)

    How can I retrive that 1G space in the logsegment without dropping the dem database ?

    Thanks ahead,

    János
    P.S. Interestingly when I do a dbcc checktable(syslogs) in 'dem' this is the output:

    1> use dem
    2> go
    1> dbcc checktable(syslogs)
    2> go
    Checking table 'syslogs' (object ID 8): Logical page size is 2048 bytes.

    Checking partition 'syslogs_8' (partition ID 8) of table 'syslogs'. The logical page size of this table is 2048 bytes.
    The total number of data pages in partition 'syslogs_8' (partition ID 8) is 44740.
    Partition 'syslogs_8' (partition ID 8) has 335283 data rows.

    The total number of data pages in this table is 44740.
    *** NOTICE: Space used on the log segment is 47812 pages (93.38 MB), 6.08%.
    *** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
    *** NOTICE: Space free on the log segment is 738620 pages (1442.62 MB), 93.92%.
    Table has 335283 data rows.
    DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
    Last edited by János Löbb; 10-08-10 at 18:06. Reason: Add more info

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by János Löbb View Post
    after the load the segments were mixxed up, having the start of the log on the 5th data device, etc..
    You need to create the database with the segments in the same sequence as the source
    select * from sysusages order by dbid,lstart

    Quote Originally Posted by János Löbb View Post
    I moved the log and dumped the database and the log, but interestingly the logsegment still indicate that 1026MB is used.
    How did you move it? What is the value of logptr
    select name, logptr from sysdatabases

  3. #3
    Join Date
    Jul 2003
    Posts
    41
    Here is the result of that select * from sysusages order by dbid,lstart:


    1> select * from sysusages order by dbid,lstart
    2> go
    dbid segmap lstart size vstart pad unreservedpgs crdate
    ------ ----------- ----------- ----------- ----------- ------ ------------- --------------------------
    1 7 0 3072 4 NULL 885 Jan 10 2008 4:40PM
    1 7 3072 29696 6660 NULL 29564 Jan 10 2008 4:41PM
    2 7 0 1536 4100 NULL 916 Jan 10 2008 4:40PM
    2 3 1536 524288 771751938 NULL 522240 Sep 13 2009 1:22PM
    2 3 525824 524288 788529154 NULL 522240 Sep 13 2009 1:22PM
    2 4 1050112 524288 805306370 NULL 522240 Sep 13 2009 1:23PM
    3 7 0 1024 3076 NULL 406 Jan 10 2008 4:40PM
    4 3 0 1048576 33554434 NULL 14 Feb 24 2008 1:54PM
    4 3 1048576 1048576 50331650 NULL 0 Feb 24 2008 1:54PM
    4 3 2097152 1048576 67108866 NULL 0 Feb 24 2008 1:54PM
    4 3 3145728 1048576 83886082 NULL 0 Feb 24 2008 1:54PM
    4 3 4194304 1048576 100663298 NULL 0 Feb 24 2008 1:54PM
    4 3 5242880 1048576 117440514 NULL 0 Feb 24 2008 1:54PM
    4 3 6291456 1048576 134217730 NULL 0 Feb 24 2008 1:54PM
    4 3 7340032 1048576 150994946 NULL 0 Feb 24 2008 1:54PM
    4 3 8388608 1048576 167772162 NULL 0 Feb 24 2008 1:54PM
    4 3 9437184 1048576 184549378 NULL 0 Feb 24 2008 1:54PM
    4 3 10485760 1048576 201326594 NULL 0 Feb 24 2008 1:54PM
    4 3 11534336 1042432 218103810 NULL 0 Feb 24 2008 1:54PM
    4 3 12576768 6144 219146242 NULL 0 Feb 24 2008 1:54PM
    4 3 12582912 517120 234881026 NULL 0 Feb 24 2008 1:54PM
    4 3 13100032 531456 235398146 NULL 0 Feb 24 2008 1:54PM
    4 3 13631488 1048576 251658242 NULL 0 Feb 24 2008 1:54PM
    4 3 14680064 1048576 268435458 NULL 0 Feb 24 2008 1:54PM
    4 3 15728640 1048576 285212674 NULL 0 Feb 24 2008 1:54PM
    4 3 16777216 1048576 301989890 NULL 0 Feb 24 2008 1:54PM
    4 3 17825792 1048576 318767106 NULL 0 Feb 24 2008 1:54PM
    4 3 18874368 1048576 335544322 NULL 0 Feb 24 2008 1:54PM
    4 4 19922944 262144 352321538 NULL 387942 Feb 24 2008 1:54PM
    4 4 20185088 251392 369098754 NULL 135414 Feb 24 2008 1:54PM
    4 4 20436480 10752 369350146 NULL 21420 Feb 24 2008 1:54PM
    4 3 20447232 1048576 520093698 NULL 0 Mar 17 2008 1:35PM
    4 3 21495808 1048576 536870914 NULL 0 Jul 10 2008 12:41PM
    4 3 22544384 1048576 553648130 NULL 0 Nov 3 2008 9:55AM
    4 3 23592960 1048576 570425346 NULL 0 Jan 16 2009 5:02PM
    4 3 24641536 1048576 721420290 NULL 0 May 20 2009 4:19PM
    4 3 25690112 1048576 754974722 NULL 0 Jul 29 2009 10:44AM
    4 3 26738688 1048576 822083586 NULL 0 Oct 26 2009 11:12AM
    4 3 27787264 1048576 838860802 NULL 0 Feb 25 2010 12:33PM
    4 3 28835840 1048576 855638018 NULL 19440 Apr 29 2010 12:37PM
    4 3 29884416 1048576 905969666 NULL 344209 Jul 13 2010 1:06PM
    4 3 30932992 1048576 922746882 NULL 1044480 Sep 15 2010 2:01PM
    31513 7 0 1024 5636 NULL 382 Jan 10 2008 4:40PM
    31514 7 0 102400 16777218 NULL 76708 Jan 10 2008 4:41PM
    31515 11 0 524288 385875970 NULL 521634 Sep 20 2010 3:24PM
    31515 19 524288 262144 452984834 NULL 261120 Sep 20 2010 3:24PM
    31515 4 786432 131072 469762050 NULL 130560 Sep 20 2010 3:24PM
    31516 11 0 262144 587202562 NULL 22895 Apr 21 2009 5:01PM
    31516 19 262144 262144 603979778 NULL 0 Apr 21 2009 5:01PM
    31516 35 524288 262144 620756994 NULL 0 Apr 21 2009 5:01PM
    31516 67 786432 262144 637534210 NULL 0 Apr 21 2009 5:01PM
    31516 4 1048576 49152 654311426 NULL 48960 Apr 21 2009 5:01PM
    31516 3 1097728 263168 738197506 NULL 36120 Jun 8 2009 11:24AM
    31516 131 1360896 262144 872415234 NULL 104044 May 12 2010 4:51PM
    31516 131 1623040 262144 889192450 NULL 153369 May 13 2010 10:36AM

    (55 rows affected)

    In the source database, that is from a 32-bit 12.5.0.3 ASE, the devices could not be bigger than 2GB. In this 64-bit 15.0.3 ASE I created devices with size 8GB. As you can see the source database was created on most of the data devices and on the three log devices on one day, and additional data devices were added later as it was needed. The database were created with a command like:
    create database sur on surdev1=2048, surdev2=2048,....,surdev19=2048 log on surlog1=512, surlog2=512
    Even at that time in 2008 after the load the future allocations were messed up and I had to fix it and it goes back the same way to version 10.0.1 in 1998. After a load I always had a mixed database and I always had to move the log. Smetimes I could do it without adding a 3rd log device, but now I could not.

    First I tried to move the log by making the database single user, than used
    sp_logdevice dem, surlog1
    then I dropped the the logsegment from surdev5 and extended the default and system segments on surdev5,
    then I inputed 200 records into a y_y_dummy table, just to move the log pointer,
    then I dumped the transaction of dem with truncate only
    then I checked the log with sp_helplog and it reported that it could not find the first page of the log.
    So that was the point when I realized that I have 1GB space tied up on the the two 512 MB logdevice, so I created a third logdevice and altered the databse with the log on to this surlog3 device.
    Then I used sp_logdivece again, truncated the y_y_dummy table and inputted 200 records into it again then I dumped again the transactions of 'dem' and at this time sp_helplog showed that the log started on surlog3.
    Sp_helpdb shows that the default, system and logsegments are on devices they should be but surlog1 and surlog2 is still full either with data, or with log or with some junk.

    Right now in the 15.0.3 ASE the log pointers are:

    1> select name, logptr from sysdatabases
    2> go
    name logptr
    ------------------------------ -----------
    master 7545
    model 1011
    tempdb 1076816
    sybsystemdb 1004
    sybsystemprocs 66728
    dem 34087787

    (6 rows affected)

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes I agree some data is still left on the log device. A reorg rebuild of the tables should clear this but it might be easier to just recreate the new db in the correct sequence i.e.
    Code:
    create database dem on datadev=36864 log on logdev=1024 for load
    alter database dem on datadev= 22528 for load
    Or less typing:
    Code:
    create database dem 
    on datadev=36864
    , logdev=1024
    , datadev=22528 
    for load
    Or sticking to your 8GB data and 512MB log sizes:
    Code:
    create database dem 
    on surdev1=8192
    , surdev2=8192
    , surdev3=8192
    , surdev4=8192
    , surdev5=4096
    , surlog1=512
    , surlog2=512
    , surdev5=4096
    , surdev6=4096
    , surdev7=4096
    , surdev8=2048
    for load

Tags for this Thread

Posting Permissions

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