What’s difference of database status in restore between 15.03 & 15.7
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)
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.
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)
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
thanks, your answer help me to understand the db status