Skip to Content
0

database status inconsistent between different machines

Mar 22, 2017 at 03:19 PM

47

avatar image

Hi, I have exactly same Sybase ASE 15.7 installed on 2 servers but the status of tempdb are different.

Server1:

1> select @@version 2> go --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Adaptive Server Enterprise/15.7/EBF 24483 SMP SP131 ONE-OFF/P/Solaris AMD64/OS 5.10/ase157sp131x/3897/64-bit/FBO/Tue Apr 7 23:55:51 2015 (1 row affected)

1> select status from sysdatabases where name like "tempdb"

2> go

status

------

13

(1 row affected)

1> sp_helpdb tempdb 2> go name db_size owner dbid created durability lobcomplvl inrowlen status ------ ------------- ----- ---- ------------ ----------- ---------- -------- --------------------------------------------------------------------------------------------- tempdb 1754.0 MB sa 2 Sep 30, 2016 no_recovery 0 NULL select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log ful l, allow wide dol rows (1 row affected) device_fragments size usage created free kbytes ------------------------------ ------------- -------------------- ------------------------- ---------------- master 4.0 MB data only Oct 10 2015 12:30AM 2344 tempdev 750.0 MB data only Oct 10 2015 12:36AM 763394 tempdev1 1000.0 MB log only Oct 10 2015 12:36AM not applicable -------------------------------------------------------------------------------------------------------------- log only free kbytes = 1019858 (return status = 0)

Server 2:

1> select @@version 2> go --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Adaptive Server Enterprise/15.7/EBF 24483 SMP SP131 ONE-OFF/P/Solaris AMD64/OS 5.10/ase157sp131x/3897/64-bit/FBO/Tue Apr 7 23:55:51 2015 (1 row affected)

1> select status from sysdatabases where name like "tempdb"

2> go

status

------

12

(1 row affected)

1> sp_helpdb tempdb 2> go name db_size owner dbid created durability lobcomplvl inrowlen status ------ ------------- ----- ---- ------------ ----------- ---------- -------- --------------------------------------------------------------------------------------------- tempdb 1754.0 MB sa 2 Feb 07, 2017 no_recovery NULL NULL select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log ful l, allow wide dol rows (1 row affected) device_fragments size usage created free kbytes ------------------------------ ------------- -------------------- ------------------------- ---------------- master 4.0 MB data only Mar 19 2013 11:37AM 2344 tempdev 750.0 MB data only Mar 19 2013 11:41AM 764748 tempdev1 1000.0 MB log only Mar 19 2013 11:41AM not applicable -------------------------------------------------------------------------------------------------------------- log only free kbytes = 1018862 (return status = 0)

As you can see above, the command "select status from sysdatabases where name like "tempdb"" returns different status of database. Can you please tell me why is that?

Regards,

Alex

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Bret Halford
Mar 22, 2017 at 03:32 PM
0

Hi Alex,

Prior to 15.7 SP100, sysdatabases.status bit 0 (integer value 1) meant that the database was being upgraded. The meaning changed in SP100 and now means that all LOB (text, image, java) values in the database have been upgraded to support the new ALTER DATABASE SHRINK command.


As upgrade is a time-sensitive operation and the LOB updates are not essential the default upgrade process does not update the LOB values. As a result, upgrade does not turn the bit on for existing user databases. I suspect that your server where you are seeing the bit set may have been installed on SP100 or higher and the one where the bit isn't set has been upgraded from an earlier version

To enable the ALTER DATABASE SHRINK command on an existing user database (and turn this bit on), run dbcc shrinkdb_setup as described in the ASE 15.7 SP100 New Features Guide: http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1570100/doc/html/jpl1348010581966.html

In your case, run shrinkdb_setup on the model database, as model is used to create tempdb after each reboot.

Cheers,

-bret

Share
10 |10000 characters needed characters left characters exceeded
Alex Ying Mar 23, 2017 at 04:16 PM
0

Many Thanks Bret, it solves my problem.

Share
10 |10000 characters needed characters left characters exceeded