Chinaunix

标题: 对于alter扩展数据库后的设备删除 (删除不了数据库了） [打印本页]

作者: kuangtu 时间: 2007-07-31 15:43
标题: 对于alter扩展数据库后的设备删除 (删除不了数据库了）
系统：Sybase ASE-12-5 suse linux
原先存在数据库cljc,使用命令alter database cljc on cljc2将数据库容量扩展。但是忘记在cljc2后面写大小了，因此
分配了512页，1M。
然后重新alter database cljc on cljc2=350进行扩展。
use master
2> go
1> select * from sysusages where dbid=db_id('cljc')
2> go
dbid segmap    lstart    size       vstart    pad unreservedpgs
      crdate
------ ----------- ----------- ----------- ----------- ------ -------------
      --------------------------
   4          3          0    768000 33554432 NULL       287365
            Jul 22 2007  7:25PM
   4          4    768000    76800 50331648 NULL       76500
            Jul 22 2007  7:25PM
   4          3    844800       512 83886080 NULL          510
            Jul 27 2007 10:59PM
   4          3    845312    179200 83886592 NULL       178500
            Jul 27 2007 11:00PM

可以看到，cljc数据库中红色的为误添加的，
我通过直接删除的方法（备份了master和其他sys_login等表）
delete sysusages where dbid=db_id('cljc') and lstart=844800
2> go

select * from sysusages where dbid=db_id('cljc')
2> go
dbid segmap    lstart    size       vstart    pad unreservedpgs
      crdate
------ ----------- ----------- ----------- ----------- ------ -------------
      --------------------------
   4          3          0    768000 33554432 NULL       287365
            Jul 22 2007  7:25PM
   4          4    768000    76800 50331648 NULL       76500
            Jul 22 2007  7:25PM
   4          3    845312    179200 83886592 NULL       178500
            Jul 27 2007 11:00PM

这样做的话，是不是就可以删除了，还是会带来其他问题？当alter扩展的时候，会不会将原先设备上面的数据也放一部分到新的设备上面？
谢谢！

现在有问题了，数据库删除不了了
1> drop database cljc
2> go
00:00000:00016:2007/07/28 03:37:16.19 server  Error: 806, Severity: 21, State: 1
00:00000:00016:2007/07/28 03:37:16.19 server  Could not find virtual page for logical page 844800  in database 'cljc'.
00:00000:00016:2007/07/28 03:37:16.19 kernel  ************************************
00:00000:00016:2007/07/28 03:37:16.19 kernel  SQL causing error : drop database cljc

00:00000:00016:2007/07/28 03:37:16.19 kernel  ************************************
00:00000:00016:2007/07/28 03:37:16.20 server  SQL Text: drop database cljc
00:00000:00016:2007/07/28 03:37:16.20 kernel  curdb = 1 tempdb = 2 pstat = 0x10000
00:00000:00016:2007/07/28 03:37:16.20 kernel  lasterror = 806 preverror = 156 transtate = 0
00:00000:00016:2007/07/28 03:37:16.20 kernel  curcmd = 297 program = isql
00:00000:00016:2007/07/28 03:37:16.22 kernel  pc 0x87e16e9 ucbacktrace+0x89(0x0,0x1,0x9302ccc,0x15,0x0)
00:00000:00016:2007/07/28 03:37:16.23 kernel  pc 0x81af765 terminate_process+0xc09(0x0,0xffffffff,0x9302ccc,0x405da82c,0x8207121)
00:00000:00016:2007/07/28 03:37:16.23 kernel  pc 0x8206f35 close_network+0x19(0x9302ccc,0x4028de00,0x405da8f0,0x84f763f,0x

00:00000:00016:2007/07/28 03:37:16.23 kernel  pc 0x8207121 hdl_default+0x49(0x8,0x6,0x15,0x1,0x405da93c)
00:00000:00016:2007/07/28 03:37:16.24 kernel  pc 0x84f763f s_handle+0xf8f(0x8,0x6,0x15,0x1,0x405da93c)
00:00000:00016:2007/07/28 03:37:16.24 kernel  pc 0x8206b88 ex_raise+0x2f8(0x8,0x6,0x15,0x1,0xce400)
00:00000:00016:2007/07/28 03:37:16.24 kernel  pc 0x825345f log_to_virt+0x7b(0x40b50c44,0xce400,0x4,0x9302ccc,0x40cdf7a

00:00000:00016:2007/07/28 03:37:16.25 kernel pc 0x83edaa8 drop_al_dbid+0x120(0x40b50b40,0x4ba417dc,0x40cdfd38,0x0,0x40cdfd3

00:00000:00016:2007/07/28 03:37:16.25 kernel pc 0x83ecb0c dropd+0x1424(0x405daf0c,0x4,0x0,0x0,0x9302ccc)
00:00000:00016:2007/07/28 03:37:16.25 kernel pc 0x83f00da dropdb+0x8e(0x415c4db4,0x9302ccc,0x40cdf7a8,0x0,0x57

00:00000:00016:2007/07/28 03:37:16.25 kernel  pc 0x84dd3cc s_execute+0x3860(0x9302ccc,0x0,0x40cdf700,0x0,0x400f8339)
00:00000:00016:2007/07/28 03:37:16.25 kernel  pc 0x84f3bb9 sequencer+0xf75(0x415cd800,0x9302ccc,0x0,0x40cdf700,0x81c27a6)
00:00000:00016:2007/07/28 03:37:16.25 kernel  pc 0x81de709 tdsrecv_language+0x2ed(0x0,0x0,0x0,0x0,0x0)
00:00000:00016:2007/07/28 03:37:16.25 kernel  pc 0x81edfd1 conn_hdlr+0x2779(0x7,0x405dba98,0x895eed31,0x0,0x0)
00:00000:00016:2007/07/28 03:37:16.25 kernel  pc 0x823f34f ex_cleanup(0x0,0x0,0x0,0x4049b900,0x14)
00:00000:00016:2007/07/28 03:37:16.26 kernel  pc 0x895eed31 init_dummy+0x8099bc11(0x0,0x4049b900,0x14,0x1,0x5374616b)
00:00000:00016:2007/07/28 03:37:16.26 kernel  end of stack trace, spid 16, kpid 1245203, suid 1
Msg 806, Level 21, State 1:
Server 'LINUX', Line 1:
Could not find virtual page for logical page 844800  in database 'cljc'.
The SQL Server is terminating this process.
缺少逻辑页844800，就是我们删除的lstart页，
我用dbcc checkdb检查了一下，没有报错。
应该怎么处理一下，我要删除这个数据库？
PS：测试数据库，没有关系。。

[ 本帖最后由 kuangtu 于 2007-7-31 16:55 编辑 ]

作者: 小无赖 时间: 2007-07-31 17:25
将数据库的status改为256,再用dbcc dbrepair(db_name,dropdb)删除数据库.

作者: kuangtu 时间: 2007-08-01 10:00
标题: 回复 #2 小无赖的帖子
我的做法：
use master
2> go
1> update sysdatabases
2> set status = 256
3> where name = "cljc"
4> go
(1 row affected)
然后，
select * from sysdatabases
2> go

cljc                               4          1 256    1    791604
            Jul 22 2007  7:28PM       Jul 28 2007  3:24AM    48
               0          0          0          0          NULL
      NULL
状态status已经设置为了256，可疑数据库，但是：
dbcc dbrepair(cljc,dropdb)
2> go
Msg 2573, Level 16, State 1:
Server 'LINUX', Line 1:
Database 'cljc' is not marked suspect. You cannot drop it with DBCC.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.

报告说没有标识为可疑(not marked suspect)，这是怎么回事情？

作者: Sybase技术专家 时间: 2007-08-01 13:51
这样删系统表肯定是不行的。
置成256或320之后要重起数据库，再dbcc dbrepair(cljc,dropdb)

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)