Our application connect to ASE DB to run sp_helpdb on specific database to collection information. It works fine on ASE 15.03 all time.

After upgrade from ASE 15.0.3 to ASE 15.7.

Our application can not collect information when database is being restored. During restore, the agent is unable to connect to the databse.

No idea if any change on the database status in resotre?

1> sp_helpdb tempdb

2> go

name   db_size       owner dbid created      durability  lobcomplvl inrowlen

         status                                                                                   

—— ————- —– —- ———— ———– ———- ——–

         —————————————————————————————–

tempdb      106.0 MB sa       2 Jun 25, 2015 no_recovery          0     NULL

         select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data, a

         llow wide dol rows

(1 row affected)

device_fragments               size          usage              

         created                   free kbytes     

—————————— ————- ——————–

         ————————- —————-

master                                6.0 MB data and log       

         Jun 25 2015 10:32AM                   2136

tempdbdev                           100.0 MB data and log       

         Jun 25 2015 10:33AM                 101908

(return status = 0)

1> sp_helpdb testdb

2> go

name   db_size       owner dbid created      durability lobcomplvl inrowlen

         status       

—— ————- —– —- ———— ———- ———- ——–

         ————-

testdb       24.0 MB sa       6 Jul 02, 2015 full                0     NULL

         don’t recover

(1 row affected)

device_fragments               size          usage              

         created                   free kbytes     

—————————— ————- ——————–

         ————————- —————-

dbdata                               12.0 MB data only          

         Jul  2 2015  1:06PM                   8832

dblog                                12.0 MB log only           

         Jul  2 2015  1:06PM       not applicable  

(return status = 0)

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Jeroen Rijnbergen

    On ASE 15.0 default value of sysdatabases.status = 0

    On ASE 15.7 default value of sysdatabases.status = 1

    This is internal data, but your application might check for it.

    Note this column is used as a bit map. If you want to check for a specific status, you should check for a specific bit.

    E.g. to check if option select into/bulkcopy is set: where status & 4 = 4

    Both in 15.0 and 15.7 the database itself is not available during a restore, nothing changed from that perspective.

    (0) 
    1. Bin Lv Post author

      Hi Jeroen,

      thanks your answer.

      My target is to calculate the data/log size/freespace/freespace percent of every DB.

      I first collect the output of sp_helpdb on secific DB.

      Do calculation according the DB’s status.

      When in restore status,

      I think it is meaningful to calculate these index of data.It’s no use to do on log.

      I just want to confirm if there are no change on the output of sp_helpdb specific db in restore status from 15.0.3 to 15.7. Our application is developed based on ct-lib API. I think the header file&inlude library files are not the latest version.

      In my two test db below (on 15.7),

      1> sp_helpdb testdb

      2> go

      name   db_size       owner dbid created      durability lobcomplvl inrowlen         status       

      —— ————- —– —- ———— ———- ———- ——–         ————-

      testdb       24.0 MB sa       6 Jul 02, 2015 full                0     NULL         don’t recover

      (1 row affected)

      device_fragments               size          usage                        created                   free kbytes     

      —————————— ————- ——————–         ————————- —————-

      dbdata                               12.0 MB data only                    Jul  2 2015  1:06PM                   8832

      dblog                                12.0 MB log only                     Jul  2 2015  1:06PM       not applicable  

      (return status = 0)

      1> sp_helpdb datalog

      2> go

      name    db_size       owner dbid created      durability lobcomplvl inrowlen         status       

      ——- ————- —– —- ———— ———- ———- ——–         ————-

      datalog       24.0 MB sa       7 Jul 16, 2015 full                0     NULL         don’t recover

      (1 row affected)

      device_fragments               size          usage                        created                   free kbytes     

      —————————— ————- ——————–         ————————- —————-

      datalog                              12.0 MB data only                    Jul 16 2015  6:17AM                   8832

      datalog                              12.0 MB log only                     Jul 16 2015  6:17AM       not applicable  

      (return status = 0)

      (0) 
      1. Jeroen Rijnbergen

        In 15.7 there’s extra columns in the output of sp_helpdb

        You can’t expect the output will stay the same between versions

        Calculating free space based on master..sysusages is possible, but it’s not 100% accurate.

        If you do query the system tables directly, you can also join with sysdatabases and only check dbs which are available

        If you do want to keep calculating statistics based on sp_helpdb, then at least avoid running on dbs that are not available (created for load, being restored, offline, being upgraded, suspect, etc.)

        To check which dbs are available, run a query on master..sysdatabases and check columns status, status2 and status3

        See the reference manual system tables for description of all possible bit fields

        (0) 

Leave a Reply