Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    54

    Unanswered: swapping tempdb drive....

    Hey Folks,
    I have been getting Msg. 691's for about three months now when the
    whole machine would be unusable.The sybase trouble shooting manual
    sugggests that it is a hardware failure but cannot find anything in
    the OS logs. We are using ASE 11.9.2 and the OS is 7.3 RH linux. Now,
    the 691 error points to the tempdb and it is on a different drive than
    master or user db's i.e it is on it's own drive. We are planning to
    swap the existing drive on which tempdb resides with another spare. I
    donot know how to recreate the tempdb on the new drive without
    rebuilding the entire server. Can someone please give the step by step
    directions in what should be done before asd after the drive is
    replaced with a newer one..........

    Regards

    Subhas

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    WARNING: This procedure is only for tempdb and if only tempdb has a fragment on master device.

    To reset tempdb to the default size, perform the following steps:

    1) Boot the SQL Server in single user mode, using the -m option with the startserver command.
    2) Log into the server as "sa"
    3) Dump the master database, (IMPORTANT)
    4) Just to be safe, perform and save select * from sysusages, select * from sysdevices, select * from sysdatabases, select * from syslogins
    5) sp_configure "allow updates", 1 -> allows updates to system tables

    6) begin tran
    go
    select * from sysusages
    where dbid = 2 -> to get all database fragments for tempdb
    go
    update sysusages set segmap=7 where dbid = 2
    and lstart = 0 -> if the first fragment of tempdb already has segmap = 7 and lstart= 0 this step is not necessary

    delete sysusages
    where dbid = 2
    and lstart !=0 -> deletes all database fragments for tempdb
    go EXCEPT the first one on the master device
    select * from sysusages
    where dbid = 2
    go -> JUST TO MAKE SURE you have only one fragment
    for tempdb and it's on the master device
    commit tran
    go -> assuming the last command showed the 2MB
    default fragment of tempdb with lstart= 0,
    vstart = 2564; otherwise, rollback transaction
    checkpoint
    go -> checkpoint the master database

    IF the size of the model database is not equal to 1024 pages or 2M, then do the following.
    dbcc dbrepair(tempdb, remap)
    go
    alter tempdb on <device> = (size of model - 2M)
    go
    NOTE: Tempdb should be equal to or greater than the size of model database.

    Restart the SQL Server. Now the devices which were in use by tempdb can be dropped using sp_dropdevice.

    After everything is back to normal, issue
    sp_configure "allow updates", 0 to turn updates for system tables back off and dump the master database.

  3. #3
    Join Date
    Apr 2003
    Posts
    54

    Thanks

    Thank you..

  4. #4
    Join Date
    Apr 2003
    Posts
    54
    hi,
    Thanks for responding but I have another question. I have a tempdb_cache which is 200 MB. Di I have to drop that before I do the entire procedure. Also, the initial allocation on the master of tempdb is data and log mixed. Is it Okay?????

    Regards


    Subhas
    Originally posted by trvishi
    WARNING: This procedure is only for tempdb and if only tempdb has a fragment on master device.

    To reset tempdb to the default size, perform the following steps:

    1) Boot the SQL Server in single user mode, using the -m option with the startserver command.
    2) Log into the server as "sa"
    3) Dump the master database, (IMPORTANT)
    4) Just to be safe, perform and save select * from sysusages, select * from sysdevices, select * from sysdatabases, select * from syslogins
    5) sp_configure "allow updates", 1 -> allows updates to system tables

    6) begin tran
    go
    select * from sysusages
    where dbid = 2 -> to get all database fragments for tempdb
    go
    update sysusages set segmap=7 where dbid = 2
    and lstart = 0 -> if the first fragment of tempdb already has segmap = 7 and lstart= 0 this step is not necessary

    delete sysusages
    where dbid = 2
    and lstart !=0 -> deletes all database fragments for tempdb
    go EXCEPT the first one on the master device
    select * from sysusages
    where dbid = 2
    go -> JUST TO MAKE SURE you have only one fragment
    for tempdb and it's on the master device
    commit tran
    go -> assuming the last command showed the 2MB
    default fragment of tempdb with lstart= 0,
    vstart = 2564; otherwise, rollback transaction
    checkpoint
    go -> checkpoint the master database

    IF the size of the model database is not equal to 1024 pages or 2M, then do the following.
    dbcc dbrepair(tempdb, remap)
    go
    alter tempdb on <device> = (size of model - 2M)
    go
    NOTE: Tempdb should be equal to or greater than the size of model database.

    Restart the SQL Server. Now the devices which were in use by tempdb can be dropped using sp_dropdevice.

    After everything is back to normal, issue
    sp_configure "allow updates", 0 to turn updates for system tables back off and dump the master database.

Posting Permissions

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