存档
本文介绍我遇到的将tempdb从master设备上移走时出现的逻辑页面读取错误问题的解决方法。
sybase版本为:Adaptive Server Enterprise/15.0.3/EBF 16550 ESD#1/P/NT (IX86)/Windows 2003/ase1 503/2680/32-bit/OPT/Thu Mar 05 00:21:40 2009
开始时tempdb在master和tempdbdev两个设备上都放置数据和日志,如下:
1> select *from sysusages
2> where dbid =2
3> go
dbid segmap lstart size vstart pad unreservedpgs
crdate vdevno
------ ----------- ----------- ----------- ----------- ------ -------------
-------------------------- -----------
2 7 0 1536 16388 NULL 694
Dec 2 2009 6:58PM 0
2 7 1536 25600 0 NULL 25500
Dec 2 2009 7:01PM 2
由上可见,ASE15版本中默认给临时数据库在tempdbdev设备分配了100m的空间,比起12版本中默认的3M大多了!
为了减少存取系统表时对I/O资源的争夺,将tempdb从master设备上分离出来。
从网上可以找到方法:
sp_dropsegment "default",tempdb,master
sp_dropsegment "system",tempdb,master
sp_dopsegment "logsegment",tempdb,master
我使用的方法是直接删除sysusages表中临时数据库使用的空间分配信息。
1> begin tran
2> go
1> delete from sysusages
2> where dbid = 2 and size =1536
3> go
(1 row affected)
1> select * from sysusages
2> where dbid =2
3> go
dbid segmap lstart size vstart pad unreservedpgs
crdate vdevno
------ ----------- ----------- ----------- ----------- ------ -------------
-------------------------- -----------
2 7 1536 25600 0 NULL 25500
Dec 2 2009 7:01PM 2
(1 row affected)
1> update sysusages
2> set lstart=0
3> where dbid = 2
4> go
(1 row affected)
1> select * from sysusages
2> go
dbid segmap lstart size vstart pad unreservedpgs
crdate vdevno
------ ----------- ----------- ----------- ----------- ------ -------------
-------------------------- -----------
1 7 0 6656 4 NULL 4200
Dec 2 2009 6:58PM 0
3 7 0 1536 13316 NULL 694
Dec 2 2009 6:58PM 0
31513 7 0 1536 19460 NULL 654
Dec 2 2009 6:58PM 0
31514 7 0 35840 0 NULL 12240
Dec 2 2009 6:59PM 1
2 7 0 25600 0 NULL 25500
Dec 2 2009 7:01PM 2
31513 7 1536 1536 0 NULL 1530
Dec 2 2009 7:01PM 3
31515 7 0 19200 0 NULL 14410
Dec 2 2009 7:03PM 4
4 3 0 76800 0 NULL 75656
Dec 7 2009 2:28PM 5
4 4 76800 25600 0 NULL 25500
Dec 7 2009 2:28PM 6
(9 rows affected)
1> select * into tempdb.dbo.#lzf
2> from sysobjects
3> go
Msg 2762, Level 16, State 3:
Server 'TEST', Line 1:
The 'CREATE TABLE' command is not allowed within a multi-statement transaction
in the 'tempdb' database.
1> commit (此时已经提交了事务,对sysusages的修改生效!)