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

    Unanswered: Removing a device from tempdb

    Hi,

    I followed Brett's advise on how to truncate back tempdb to master and add a new device later. So here is what I need to do and here is what I did.
    I upgraded from 11.0.3 to 11.9.2.6 on AIX 4.3.3. Tempdb still used a device "sybtmp1" from /sybase/system11/tempdb/sybtmp1.dat. I wanted the server to give up on this device - because I want to eliminate the whole /sybase/system11 directory and I created a new device 'sybtemp1' on /sybase/system1192/tempdb/sybtemp1.dat. First a did the following:

    1> sp_configure "allow u",1
    2> go
    1> delete sysusages where dbid=2 and lstart >0
    2> go
    1> sp_configure "allow u",0
    2> go

    then I shutdown the server

    1> shutdown
    2> go
    Server SHUTDOWN by request.
    The SQL Server is terminating this process.

    After start, I had the following in my log:

    00:00000:00001:2003/07/17 12:45:58.80 server Clearing temp db
    00:00000:00001:2003/07/17 12:46:00.46 server Error: 806, Severity: 21, State: 1
    00:00000:00001:2003/07/17 12:46:00.46 server Could not find virtual page for logical page 1024 in database 'tempdb'.

    Then when I tried to alter tempdb for the now device, I got tons of errors:

    1> use master
    2> go
    1> alter database tempdb on sybtemp1=512
    2> go
    01:00000:00001:2003/07/17 12:48:58.66 server Error: 834, Severity: 20, State: 4
    01:00000:00001:2003/07/17 12:48:58.66 server Illegal attempt to clean buffer: BUF pointer = '0x4f7e2990', MASS pointer = '0x4f7e2990', (Buf#: '0'), page ptr = '0x4e4cc800', dbid = '2', Mass virtpage = '1540', Buffer page = '0', Mass status = '0x808', Buffer status = '0x1', size = '2048', cache (id: 0) = 'default data cache'.
    01:00000:00001:2003/07/17 12:48:58.75 server Error: 834, Severity: 20, State: 4
    01:00000:00001:2003/07/17 12:48:58.75 server Illegal attempt to clean buffer: BUF pointer = '0x4f7e2990', MASS pointer = '0x4f7e2990', (Buf#: '0'), page ptr = '0x4e4cc800', dbid = '2', Mass virtpage = '1540', Buffer page = '0', Mass status = '0x808', Buffer status = '0x1', size = '2048', cache (id: 0) = 'default data cache'.
    01:00000:00001:2003/07/17 12:48:58.75 kernel ************************************
    01:00000:00001:2003/07/17 12:48:58.75 kernel SQL causing error : alter database tempdb on sybtemp1=512

    01:00000:00001:2003/07/17 12:48:58.75 kernel ************************************
    01:00000:00001:2003/07/17 12:48:58.75 server SQL Text: alter database tempdb on sybtemp1=512
    01:00000:00001:2003/07/17 12:48:58.76 kernel curdb = 1 pstat = 0x10000 lasterror = 834
    Msg 834, Level 20, State 4:
    01:00000:00001:2003/07/17 12:48:58.76 kernel preverror = 0 transtate = 0
    Line 1:
    Illegal attempt to clean buffer: BUF pointer = '0x4f7e2990', MASS pointer =
    '0x4f7e2990', (Buf#: '0'), page ptr = '0x4e4cc800', dbid = '2', Mass virtpage =
    '1540', Buffer page = '0', Mass status = '0x808', Buffer status = '0x1', size =
    '2048', cache (id: 0) = 'default data cache'.
    01:00000:00001:2003/07/17 12:48:58.76 kernel curcmd = 215 program = ctisql
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x10010c14 pcstkwalk+0x3c()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x100112b8 ucstkgentrace+0x1b8()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x1000fd5c ucbacktrace+0xcc()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x1000da94 terminate_process+0x8ac()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x10011934 close_network+0x18()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x100116d4 hdl_default+0x58()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x100e93e0 s_handle+0x510()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x10011d78 ex_raise+0x3a0()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x10090bb4 cm_dbclean+0xf0()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x1073555c bufdbclean+0x58()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x102ada94 dbt__removeall+0x2c()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x102b04b4 dbt_get+0x450()
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x1047a408 extend+0x294()
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x1047babc alterdb+0x6dc()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x100fb7e4 s_execute+0x3aec()
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x100e8ed0 s_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x106e2d38 sequencer+0xe90()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x107307b8 tdsrecv_language+0x3a4()
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x10011790 hdl_backout installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel [Handler pc: 0x1006496c ut_handle installed by the following function:-]
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x10730304 conn_hdlr+0x18ec()
    01:00000:00001:2003/07/17 12:48:58.76 kernel pc: 0x106c37a8 kpcoldstart+0x1c()
    01:00000:00001:2003/07/17 12:48:58.76 kernel end of stack trace, spid 1, kpid 983055, suid 1
    CT-LIBRARY error:
    ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect
    CT-LIBRARY error:
    ct_cancel(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect

    Now looks like tempdb is not recovered and of course not sybsystemprocs either.

    Any good advice ?

    Thanks ahead,
    János

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya,

    The steps atht you perform assume that your tempdb fragment on master device is still there in sysusages.

    If you had previously removed the tempdb fragment on master device then this step won't work.

    Cheers,

    Willy

  3. #3
    Join Date
    Jul 2003
    Posts
    41
    Yes, I still have the 2MB fragment on master with segmap 7. The problem is that I extended model to "minimum 3MB" according to pre-upgrade Tasks by Sybase, - actually I extended it to 5MB -, so now the 5MB model is not fitting into the 2MB fragmant still reserved for tempdb on master.

    I tried the followings:

    - using "master..sp_configure 'allow u',1" - it failed and kicked me out from isql for invalid buffer modification /error 834 state 4/.

    - tried to extend tempdb on master by adding a 4MB fragment to it with "alter tempdb on master=4" - it just hangs the session, so I have to shutdown the server with nowait.

    - tried to add back one of the deleted entries to master..sysusages, but of course first sso has to flip the "allow updates to system tables" switch, - so I am in a catch 22 situation because sysbsystemprocs is not recovered yet and it is not online.

    - I did not dump master immediately after the upgrade, so I do not have a 11.9.2.6 dump of it so I cannot do a re-load.

    - I am thinking to bypass recovery for sybsystemprocs, and then I will have access to sp_... procedures. It is possible that all sp_procedures are using tempdb, so it might not work either. I am also afraid that some little unexpected and unknown devils will pop into action as I go along that way.

    Looks like I need here may be a special dbcc call which does not use tempdb in any shape or form to either shrink back model into 2MB - I already read that it might not be an option -, or to extend tempdb on master to 5MB.

    Any good advise is well appreciated.

    János

  4. #4
    Join Date
    Dec 2002
    Posts
    3

    Re: Removing a device from tempdb

    sorry my English is not good, but send solution:

    -- Device for DB TempDB is a FileSystem. Not RawDevice!!!
    -- owner and group Device = sybase account
    -- Size DB Tempdb is 80% of the Device
    -- Device : 1000 MB (1000 * 512 = 512000)
    -- DB Tempd : 800MB (1000 * 0.8 = 800), else Problems because full logsegment

    Solution sybase 11.9 and 12 (before read and apply carefully)

    use master
    go
    sp_configure "allow updates to system tables",1
    go
    reconfigure with override
    go
    begin tran
    go
    delete master..sysusages where dbid=2
    delete master..sysdatabases where dbid=2
    go
    commit tran
    go
    (If exist device, delete them)
    sp_dropdevice tempdb00
    -- in unix : rm /usr1/sybase/devices/tempdb/tempdb00
    go
    -- new Device 1000MB, but 800MB used (1000*512=512000)

    disk init name="tempdb00" ,physname="/usr1/sybase/devices/tempdb/tempdb00", vdevno=2,size=512000

    sp_configure "allow updates to system tables",0
    go
    reconfigure with override
    go
    -- Remember.... TEMPDB 800MB with device size 1000MB
    create database tempdb on tempdb00=800
    go

    sp_configure "allow updates to system tables",1
    go
    update sysusages set dbid=2 where dbid=db_id("tempdb")
    update sysdatabases set dbid=2,name="tempdb" where dbid=db_id("tempdb")
    go
    sp_configure "allow updates to system tables",0
    go

    sp_dboption tempdb,"select into/bulkcopy/pllsort",true
    go
    use tempdb
    go
    checkpoint
    go

    Saludos

  5. #5
    Join Date
    Dec 2002
    Posts
    3

    Re: Removing a device from tempdb

    Originally posted by lordenes
    sorry my English is not good, but send solution:

    -- Device for DB TempDB is a FileSystem. Not RawDevice!!!
    -- owner and group Device = sybase account
    -- Size DB Tempdb is 80% of the Device
    -- Device : 1000 MB (1000 * 512 = 512000)
    -- DB Tempd : 800MB (1000 * 0.8 = 800), else Problems because full logsegment

    Solution sybase 11.9 and 12 (before read and apply carefully)

    use master
    go
    sp_configure "allow updates to system tables",1
    go
    reconfigure with override
    go
    begin tran
    go
    delete master..sysusages where dbid=2
    delete master..sysdatabases where dbid=2
    go
    commit tran
    go
    (If exist device, delete them)
    sp_dropdevice tempdb00
    -- in unix : rm /usr1/sybase/devices/tempdb/tempdb00
    go
    -- new Device 1000MB, but 800MB used (1000*512=512000)

    disk init name="tempdb00" ,physname="/usr1/sybase/devices/tempdb/tempdb00", vdevno=2,size=512000

    sp_configure "allow updates to system tables",0
    go
    reconfigure with override
    go
    -- Remember.... TEMPDB 800MB with device size 1000MB
    create database tempdb on tempdb00=800
    go

    sp_configure "allow updates to system tables",1
    go
    update sysusages set dbid=2 where dbid=db_id("tempdb")
    update sysdatabases set dbid=2,name="tempdb" where dbid=db_id("tempdb")
    go
    sp_configure "allow updates to system tables",0
    go

    sp_dboption tempdb,"select into/bulkcopy/pllsort",true
    go
    use tempdb
    go
    checkpoint
    go

    Saludos

    Otra cosa, los dispositivos (Device) crealos con el tamańo que estimes conveniente. los 1000MB son solo de ejemplo , pero puede ser menor

    Durante el cambio en Sybase, pueden aparecer mensajes indicando problemas con la tempdb, pero es normal... asi que no te asustes!!!!

    Una vez que se cree la nueva TEMPDB, debes bajar Sybase y a continuacion subirla.

    -- in Sybase
    use master
    go
    checkpoint
    go
    shutdown with no_wait
    go

    -- In Unix:
    cd install
    startserver -f RUN_SERVER

Posting Permissions

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