on 01-03-2017 6:33 PM
Hello,
I might be losing my mind here. We appeared to successfully drop a database but it still showed up in sp_helpdb and in sysdatabases:
1> select dbid, name from sysdatabases
2> go | egrep "master|xxx_trading"
1 master
15 xxx_trading
When I attempted to use the database I thought was dropped, I was actually put in master:
1> use xxx_trading
2> go
1> select db_name()
2> go
------------------------------
master
(1 row affected)
What really concerned me here was that I was able to "use xxx_trading" and issue a create table command without error. The table showed up in master. I expected an error like 823 when I created the table.
We then shutdown ASE and restarted it, the database was marked appropriately offline (because we had removed its underlying devices during the original drop database process). We were then able to drop the database and its devices without error.
Has anyone seen where you "use database_xxx" that you know should at least be offline and get put into master?
What ASE version do you run ?
Check the various status bits in master..sysdatabases for the database "xxx_trading".
What does full output of sp_helpdb show ?
What does select db_name() show after you execute "use xxx_trading" and it succeeds/fails ?
As Bret says, use <dbname> does not fail silently ? So can you check the error status after that command ?
You may be able to off line the database using new dbcc dbreboot command. (Use at your discretion)
More details on the command at link below
https://wiki.scn.sap.com/wiki/display/SYBASE/DBCC+dbreboot
Avinash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Neal,
When you issued the "use xxx_trading" command, what database were you in?
I suspect you were already in master and the USE command failed silently and simply left you in master.
What were the exact sequence of commands you used to drop the database and devices?
I don't recall seeing "use database" fail silently before.
-bret
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Bret,
Happy New Year!
I got their ASE error log and have a little more background and I think your explanation makes sense. The specific version string:
Adaptive Server Enterprise/15.7/EBF 23820 SMP SP132 /P/RS6000/AIX 6.1/ase157sp131x/3919/64-bit/FBO/Tue Jan 20 04:15:36 2015
We attempted to do an "UNMOUNT DATABASE xxx_trading to '/some/manifestfile' WITH OVERRIDE". This failed with:
ASE message 905 : Unable to allocate a DBTABLE descriptor to open database 'ams_trading'. Close or drop another database before opening this one, or ask your System Administrator to raise the configuration parameter 'number of open databases'.
When the UNMOUNT fails, we fallback to trying a "DROP DATABASE". Before unmounting the database, we unmount the NFS share where the database devices live so that the "DROP DATABASE" does not write to the devices:
SELECT d.name AS device_name, SUM(u.size) AS device_size, d.phyname as device_path FROM master..sysusages u, master..sysdevices d WHERE d.vdevno=u.vdevno AND dbid=db_id('xxx_trading') GROUP BY d.name
ASE message 823 : I/O error detected during read for BUF=0x0000000168169520, MASS=0x0000000168169520, Buf#=0, page=0x000000016800a800, dbid=15, Mass vdevno=92, vpage=27, Buf lpage=25, Mass stat=0x4000100, Buf stat=0x1, size=2048, cid=0 ('default data cache'), Pinned xdes=0x0000000000000000, spid=0.
DROP DATABASE xxx_trading
ASE message 3758 : Cannot drop this database because ASE cannot determine its status. Please use DBCC DBREPAIR(xxx_trading, DROPDB) to drop this database.
And then I experienced the weirdness with "use xxx_trading".
I tried reproducing with a slightly older version (Adaptive Server Enterprise/15.7.0/EBF 19803 SMP ESD#01 /P/RS6000/AIX 6.1/aseasap/2918/64-bit/FBO/Wed Feb 8 06:09:12 2012) but I'm having trouble getting error 905 even after setting the number of open databases down to 6.
I tried something slightly different. I unmounted the NFS directory containing my devices and tried the UNMOUNT command:
1> UNMOUNT DATABASE Vpine to "/tmp/Vpine.manifest" with OVERRIDE
2> go
Msg 823, Level 24, State 1:
Server 'AIX10114_S1', Line 1:
I/O error detected during read for BUF=0x000000018544df70, MASS=0x000000018544df70, Buf#=0, page=0x0000000182fc3000, dbid=0, Mass vdevno=98, vpage=2, Buf lpage=0, Mass stat=0x110, Buf stat=0x0, size=4096, cid=0 ('default
data cache'), Pinned xdes=0x0000000000000000, spid=0.
ASE is terminating this process.
isql -Usa -SAIX10114_S1 -w220 -Psybase
1> use Vpine
2> go
1> select db_name()
2> go
------------------------------
Vpine
(1 row affected)
1> drop database Vpine
2> go
Msg 823, Level 24, State 1:
Server 'AIX10114_S1', Line 1:
I/O error detected during read for BUF=0x000000018544d3a8, MASS=0x000000018544d3a8, Buf#=0, page=0x0000000182fb6000, dbid=9, Mass vdevno=98, vpage=690, Buf lpage=344, Mass stat=0x4000100, Buf stat=0x1, size=4096, cid=0
('default data cache'), Pinned xdes=0x0000000000000000, spid=0.
1> sp_helpdb
2> go
name db_size owner dbid created durability lobcomplvl inrowlen status
---------------------------- ------------- ----- ----- ------------ ----------- ---------- -------- -----------------------------------------------------------------------------------------
Vpine 250.0 MB sa 9 Dec 29, 2016 full 0 0 select into/bulkcopy/pllsort, trunc log on chkpt
So my questions:
Adaptive Server Enterprise/15.7/EBF 22235 SMP SP121 /P/Solaris AMD64/OS 5.10/ase157sp12x/3660/64-bit/FBO/Thu Mar 20 06:07:20 2014
1> drop database Vdb2
2> go
Msg 823, Level 24, State 1:
Server 'nstacksolasestg', Line 1:
I/O error detected during read for BUF=0xfffffd7ffda7e718, MASS=0xfffffd7ffda7e718, Buf#=0, page=0xfffffd7ffd552000, dbid=11684, Mass vdevno=43, vpage=1520, Buf lpage=760, Mass stat=0x4000100, Buf stat=0x1, size=4096,
cid=0 ('default data cache'), Pinned xdes=0x0000000000000000, spid=0.
1> sp_helpdb
2> go
name db_size owner dbid created durability lobcomplvl inrowlen status
-------------- ------------- ----- ----- ------------ ----------- ---------- -------- -----------------------------------------------------------------------------------------
master 26.0 MB sa 1 Aug 29, 2016 full 0 NULL mixed log and data
model 6.0 MB sa 3 Aug 29, 2016 full 0 NULL mixed log and data
pubs2 7.0 MB sa 4 Aug 29, 2016 full 0 NULL trunc log on chkpt, mixed log and data
sybsystemdb 6.0 MB sa 31513 Aug 29, 2016 full 0 NULL mixed log and data
sybsystemprocs 172.0 MB sa 31514 Aug 29, 2016 full 0 NULL trunc log on chkpt, mixed log and data
tempdb 6.0 MB sa 2 Jan 03, 2017 no_recovery 0 NULL select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data, allow wide dol rows
Thanks,
Neal
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.