2,598
社区成员
发帖
与我相关
我的任务
分享can't allocate space,sybase问题，高手帮忙看看（汪海）
1.can't allocate space for object "mid_history_ls" in database "bocdb" because the 'default' segment in syslogs,dump the transaction log,otherwise,use alter database or sp_extendsegment to increase the size of the segment
2.sort faild:out of space in database 'bocdb'
3.config:aio_breakup-AIO request table overflow (NAIOREQ=120 exceeded)
其中bocdb是数据库的名字，表mid_history_ls是我后创建的中间表，我在往这个中间表中装数据时出现的如上三个错误，日志是每一分钟自动dump tran bocdb with truncate_only的，以上问题该如何解决，麻烦高手给个指点！
日志空间因该是够的，tempdb大概是7G的那，mid_history_ls是我后来创建的，领一个表r_history_ls装的数据比 mid_history_ls还要多的那，我是从r_history_ls中抽取数据到mid_history_ls中的，高手看看如何解决
2.sort faild:out of space in database 'bocdb'
3.config:aio_breakup-AIO request table overflow (NAIOREQ=120 exceeded)
其中bocdb是数据库的名字，表mid_history_ls是我后创建的中间表，我在往这个中间表中装数据时出现的如上三个错误，日志是每一分钟自动dump tran bocdb with truncate_only的，以上问题该如何解决，麻烦高手给个指点！
日志空间因该是够的，tempdb大概是7G的那，mid_history_ls是我后来创建的，领一个表r_history_ls装的数据比 mid_history_ls还要多的那，我是从r_history_ls中抽取数据到mid_history_ls中的，高手看看如何解决
...全文
请发表友善的回复…
发表回复
rover_sh 2003-02-23
- 打赏
- 举报
谢谢！因为忙着辞职和个人的事情，所以没有及时上网的！不好意思。我想是数据空间不够了，你现在看到的是我两个月前配置数据库的信息的。谢谢您们！非常感谢的。
flyingfoxer 2003-02-21
- 打赏
- 举报
问题一的解决办法：
检查为bocdb分配的数据设备使用情况，若还有剩余空间则edit具体分配给bocdb的数据设备大小，加大几十M就够眼前用的了。
检查为bocdb分配的数据设备使用情况，若还有剩余空间则edit具体分配给bocdb的数据设备大小，加大几十M就够眼前用的了。
rover_sh 2003-02-21
- 打赏
- 举报
【zengpan_panpan() 】tempdb 7002.0 MB sa 2
Dec 20, 2002
select into/bulkcopy
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- -----------
master 2.0 MB data only 0
tempdbs 2000.0 MB log only 2046496
tempdbs1 999.0 MB data only 1022976
tempdbs1 1.0 MB log only 1024
templogdbs 2000.0 MB log only 2048000
templogdbs1 2000.0 MB data only 2032160
(return status = 0)
bocdb 9024.0 MB bocs 5
Dec 10, 2002
select into/bulkcopy, trunc log on chkpt, abort tran on log full
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- -----------
logdbs 1024.0 MB log only 1048560
userdbs 2000.0 MB data only 1088
userdbs1 2000.0 MB data only 1002400
userdbs2 2000.0 MB data only 2048000
userdbs3 2000.0 MB data only 2048000
(return status = 0)
1>
在 bocs.cfg的文件中配置如下： cache size = 10M
procedure cache percent = 45
[Named Cache:default data cache]
cache size = 10M
cache status = default data cache
[Named Cache:tmp_cache]
cache size = 10M
cache status = mixed cache
[Physical Memory]
total memory = 32768
[Lock Manager]
number of locks = 10000
就r_history_ls 每天增加10万条的流水，
【具体信息可以看如下的联结】
http://expert.csdn.net/Expert/topic/1281/1281275.xml?temp=6.556338E-02
http://expert.csdn.net/Expert/topic/1281/1281273.xml?temp=.3933832
后来tempdb又增加了2G的，
Dec 20, 2002
select into/bulkcopy
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- -----------
master 2.0 MB data only 0
tempdbs 2000.0 MB log only 2046496
tempdbs1 999.0 MB data only 1022976
tempdbs1 1.0 MB log only 1024
templogdbs 2000.0 MB log only 2048000
templogdbs1 2000.0 MB data only 2032160
(return status = 0)
bocdb 9024.0 MB bocs 5
Dec 10, 2002
select into/bulkcopy, trunc log on chkpt, abort tran on log full
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- -----------
logdbs 1024.0 MB log only 1048560
userdbs 2000.0 MB data only 1088
userdbs1 2000.0 MB data only 1002400
userdbs2 2000.0 MB data only 2048000
userdbs3 2000.0 MB data only 2048000
(return status = 0)
1>
在 bocs.cfg的文件中配置如下： cache size = 10M
procedure cache percent = 45
[Named Cache:default data cache]
cache size = 10M
cache status = default data cache
[Named Cache:tmp_cache]
cache size = 10M
cache status = mixed cache
[Physical Memory]
total memory = 32768
[Lock Manager]
number of locks = 10000
就r_history_ls 每天增加10万条的流水，
【具体信息可以看如下的联结】
http://expert.csdn.net/Expert/topic/1281/1281275.xml?temp=6.556338E-02
http://expert.csdn.net/Expert/topic/1281/1281273.xml?temp=.3933832
后来tempdb又增加了2G的，
rover_sh 2003-02-21
- 打赏
- 举报
每天10万笔交易到r_history_ls表，然后到mid_history_ls大概6－7万笔记录，分那么点还是会存在问题得那！谢谢 flyingfoxer
【zengpan_panpan()】，您有什么解决办法吗？
【zengpan_panpan()】，您有什么解决办法吗？
zengpan_panpan 2003-02-21
- 打赏
- 举报
Application Management
----------------------
Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
To High Priority 0.0 0.0 2 2.2 %
To Medium Priority 0.9 0.2 51 56.0 %
To Low Priority 0.6 0.1 38 41.8 %
------------------------- ------------ ------------ ----------
Total Priority Changes 1.5 0.4 91
Allotted Slices Exhausted per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
High Priority 0.0 0.0 0 0.0 %
Medium Priority 0.7 0.2 44 100.0 %
Low Priority 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Slices Exhausted 0.7 0.2 44
Skipped Tasks By Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Engine Skips 0.0 0.0 0 n/a
Engine Scope Changes 0.0 0.0 0 n/a
===============================================================================
ESP Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
ESP Requests 0.0 0.0 0 n/a
===============================================================================
Housekeeper Task Activity
-------------------------
per sec per xact count % of total
------------ ------------ ----------
Garbage Collections 0.3 0.1 19 n/a
Pages Processed in GC 0.0 0.0 0 n/a
Statistics Updates 0.3 0.1 19 n/a
===============================================================================
----------------------
Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
To High Priority 0.0 0.0 2 2.2 %
To Medium Priority 0.9 0.2 51 56.0 %
To Low Priority 0.6 0.1 38 41.8 %
------------------------- ------------ ------------ ----------
Total Priority Changes 1.5 0.4 91
Allotted Slices Exhausted per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
High Priority 0.0 0.0 0 0.0 %
Medium Priority 0.7 0.2 44 100.0 %
Low Priority 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Slices Exhausted 0.7 0.2 44
Skipped Tasks By Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Engine Skips 0.0 0.0 0 n/a
Engine Scope Changes 0.0 0.0 0 n/a
===============================================================================
ESP Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
ESP Requests 0.0 0.0 0 n/a
===============================================================================
Housekeeper Task Activity
-------------------------
per sec per xact count % of total
------------ ------------ ----------
Garbage Collections 0.3 0.1 19 n/a
Pages Processed in GC 0.0 0.0 0 n/a
Statistics Updates 0.3 0.1 19 n/a
===============================================================================
zengpan_panpan 2003-02-21
- 打赏
- 举报
另外你可以用sp_sysmon检测一下重负荷下的结果。
11.9版本用sp_sysmon "00:01:00" 检测1分钟，11.0版本用sp_sysmon 1
给你我的结果看看，你的也贴出来比一下。
1> sp_sysmon "00:01:00"
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
===============================================================================
Sybase Adaptive Server Enterprise System Performance Report
===============================================================================
Server Version: Adaptive Server Enterprise/11.9.2/1075/P/Linux Intel/Lin
Server Name: BBS
Run Date: Feb 21, 2003
Statistics Cleared at: 19:20:33
Statistics Sampled at: 19:21:33
Sample Interval: 00:01:00
===============================================================================
Kernel Utilization
------------------
Engine Busy Utilization
Engine 0 29.1 %
Engine 1 25.0 %
----------- --------------- ----------------
Summary Total 54.1 % Average 27.0 %
CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total CPU Yields 0.0 0.0 0 n/a
Network Checks
Non-Blocking 53685.8 12533.7 3221150 99.8 %
Blocking 102.8 24.0 6169 0.2 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 53788.7 12557.7 3227319
Avg Net I/Os per Check n/a n/a 0.00589 n/a
Disk I/O Checks
Total Disk I/O Checks 54154.0 12643.0 3249242 n/a
Checks Returning I/O 0.0 0.0 0 0.0 %
===============================================================================
Worker Process Management
-------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Worker Process Requests
Total Requests 0.0 0.0 0 n/a
Worker Process Usage
Total Used 0.0 0.0 0 n/a
Max Ever Used During Sample 0.0 0.0 0 n/a
Memory Requests for Worker Processes
Total Requests 0.0 0.0 0 n/a
===============================================================================
Parallel Query Management
-------------------------
Parallel Query Usage per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Parallel Queries 0.0 0.0 0 n/a
Merge Lock Requests per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Requests 0.0 0.0 0 n/a
Sort Buffer Waits per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Waits 0.0 0.0 0 n/a
===============================================================================
Task Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Connections Opened 0.2 0.0 11 n/a
Task Context Switches by Engine
Engine 0 257.9 60.2 15472 40.4 %
Engine 1 380.4 88.8 22825 59.6 %
------------------------- ------------ ------------ ----------
Total Task Switches: 638.3 149.0 38297
Task Context Switches Due To:
Voluntary Yields 5.3 1.2 317 0.8 %
Cache Search Misses 15.2 3.5 912 2.4 %
System Disk Writes 0.5 0.1 30 0.1 %
I/O Pacing 2.4 0.5 141 0.4 %
Logical Lock Contention 0.0 0.0 1 0.0 %
Address Lock Contention 0.0 0.0 0 0.0 %
Log Semaphore Contention 0.0 0.0 1 0.0 %
Group Commit Sleeps 0.1 0.0 7 0.0 %
Last Log Page Writes 4.4 1.0 263 0.7 %
Modify Conflicts 0.0 0.0 2 0.0 %
I/O Device Contention 137.1 32.0 8224 21.5 %
Network Packet Received 146.0 34.1 8757 22.9 %
Network Packet Sent 170.9 39.9 10255 26.8 %
SYSINDEXES Lookup 0.0 0.0 0 0.0 %
Other Causes 156.5 36.5 9387 24.5 %
===============================================================================
11.9版本用sp_sysmon "00:01:00" 检测1分钟，11.0版本用sp_sysmon 1
给你我的结果看看，你的也贴出来比一下。
1> sp_sysmon "00:01:00"
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
===============================================================================
Sybase Adaptive Server Enterprise System Performance Report
===============================================================================
Server Version: Adaptive Server Enterprise/11.9.2/1075/P/Linux Intel/Lin
Server Name: BBS
Run Date: Feb 21, 2003
Statistics Cleared at: 19:20:33
Statistics Sampled at: 19:21:33
Sample Interval: 00:01:00
===============================================================================
Kernel Utilization
------------------
Engine Busy Utilization
Engine 0 29.1 %
Engine 1 25.0 %
----------- --------------- ----------------
Summary Total 54.1 % Average 27.0 %
CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total CPU Yields 0.0 0.0 0 n/a
Network Checks
Non-Blocking 53685.8 12533.7 3221150 99.8 %
Blocking 102.8 24.0 6169 0.2 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 53788.7 12557.7 3227319
Avg Net I/Os per Check n/a n/a 0.00589 n/a
Disk I/O Checks
Total Disk I/O Checks 54154.0 12643.0 3249242 n/a
Checks Returning I/O 0.0 0.0 0 0.0 %
===============================================================================
Worker Process Management
-------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Worker Process Requests
Total Requests 0.0 0.0 0 n/a
Worker Process Usage
Total Used 0.0 0.0 0 n/a
Max Ever Used During Sample 0.0 0.0 0 n/a
Memory Requests for Worker Processes
Total Requests 0.0 0.0 0 n/a
===============================================================================
Parallel Query Management
-------------------------
Parallel Query Usage per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Parallel Queries 0.0 0.0 0 n/a
Merge Lock Requests per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Requests 0.0 0.0 0 n/a
Sort Buffer Waits per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Waits 0.0 0.0 0 n/a
===============================================================================
Task Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Connections Opened 0.2 0.0 11 n/a
Task Context Switches by Engine
Engine 0 257.9 60.2 15472 40.4 %
Engine 1 380.4 88.8 22825 59.6 %
------------------------- ------------ ------------ ----------
Total Task Switches: 638.3 149.0 38297
Task Context Switches Due To:
Voluntary Yields 5.3 1.2 317 0.8 %
Cache Search Misses 15.2 3.5 912 2.4 %
System Disk Writes 0.5 0.1 30 0.1 %
I/O Pacing 2.4 0.5 141 0.4 %
Logical Lock Contention 0.0 0.0 1 0.0 %
Address Lock Contention 0.0 0.0 0 0.0 %
Log Semaphore Contention 0.0 0.0 1 0.0 %
Group Commit Sleeps 0.1 0.0 7 0.0 %
Last Log Page Writes 4.4 1.0 263 0.7 %
Modify Conflicts 0.0 0.0 2 0.0 %
I/O Device Contention 137.1 32.0 8224 21.5 %
Network Packet Received 146.0 34.1 8757 22.9 %
Network Packet Sent 170.9 39.9 10255 26.8 %
SYSINDEXES Lookup 0.0 0.0 0 0.0 %
Other Causes 156.5 36.5 9387 24.5 %
===============================================================================
zengpan_panpan 2003-02-21
- 打赏
- 举报
你公司内的机器7000来条记录占了5M多，那个服务器上一天10万条，最多也就60-70M。
你的数据库空间确实是够用的。bocdb后两个设备根本就没用到，tempdb里面的templogdbs日志设备也没用到。
你的bocdb有没有配置过段?
另外r_history_ls表转到mid_history_ls表的过程中是不是有排序。排序要大量占用日志段。bocdb的日志设备是不是够大？
另外你的机器到底多大内存？
[Physical Memory]
total memory = 32768
这个是不是太小。
我的机器2G内存，total memory用去1G
这个配置对速度影响很大的。
还有你的存储过程多不多，感觉procedure cache percent = 45太大了，除非你的存储过程很多。
你的数据库空间确实是够用的。bocdb后两个设备根本就没用到，tempdb里面的templogdbs日志设备也没用到。
你的bocdb有没有配置过段?
另外r_history_ls表转到mid_history_ls表的过程中是不是有排序。排序要大量占用日志段。bocdb的日志设备是不是够大？
另外你的机器到底多大内存？
[Physical Memory]
total memory = 32768
这个是不是太小。
我的机器2G内存，total memory用去1G
这个配置对速度影响很大的。
还有你的存储过程多不多，感觉procedure cache percent = 45太大了，除非你的存储过程很多。
zengpan_panpan 2003-02-20
- 打赏
- 举报
sp_helpdb显示了数据库大小。它说你的default段满了,你还可以检查一下default段配置。给你个我的score数据库的例子。
1> use score
2> go
1> sp_helpdb score
2> go
name db_size owner dbid created status
------------------------ ------------- ------------------------ ------ -------------- ---------------------------------------------------------------
---------------------------------------
score 6000.0 MB score 7 Jun 17, 2000 trunc log on chkpt
(1 row affected)
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- -----------
dev_03 2000.0 MB log only 2047968
dev_04 2000.0 MB data only 0
dev_09 2000.0 MB data only 1875392
device segment
------------------------------ ------------------------------------------------------------------------------------------
dev_03 logsegment
dev_04 default
dev_04 system
dev_09 default
dev_09 system
(return status = 0)
1> sp_helpsegment "default"
2> go
segment name status
------- ------------------------------ ------
1 default 1
device size free_pages
------------------------------ ---------------------- -----------
dev_04 2000.0MB 0
dev_09 2000.0MB 937696
table_name index_name indid
------------------------------ ------------------------------ ------
syscomments syscomments 1
sysreferences csysreferences 1
sysreferences ncsysreferences 2
sysreferences nc2sysreferences 3
sysusermessages csysusermessages 1
sysusermessages ncsysusermessages 2
user_bank_detail_u pk_user_bank_detail_u 1
user_bank_detail_u_1 pk_user_bank_detail_u_1 1
user_bank_detail_u_1_2 pk_user_bank_detail_u_1_2 1
user_bank_detail_u_1_2_3 pk_user_bank_detail_u_1_2_3 1
user_bank_detail_u_1_3 pk_user_bank_detail_u_1_3 1
user_bank_detail_u_2 pk_user_bank_detail_u_2 1
user_bank_detail_u_2_3 pk_user_bank_detail_u_1_3 1
user_bank_detail_u_3 pk_user_bank_detail_u_3 1
user_trade i_user_trade_key 1
total_size total_pages free_pages used_pages
---------------------- ----------- ----------- -----------
4000.0MB 2048000 937696 1110304
(return status = 0)
你自己分析一下啦。
1> use score
2> go
1> sp_helpdb score
2> go
name db_size owner dbid created status
------------------------ ------------- ------------------------ ------ -------------- ---------------------------------------------------------------
---------------------------------------
score 6000.0 MB score 7 Jun 17, 2000 trunc log on chkpt
(1 row affected)
device_fragments size usage free kbytes
------------------------------ ------------- -------------------- -----------
dev_03 2000.0 MB log only 2047968
dev_04 2000.0 MB data only 0
dev_09 2000.0 MB data only 1875392
device segment
------------------------------ ------------------------------------------------------------------------------------------
dev_03 logsegment
dev_04 default
dev_04 system
dev_09 default
dev_09 system
(return status = 0)
1> sp_helpsegment "default"
2> go
segment name status
------- ------------------------------ ------
1 default 1
device size free_pages
------------------------------ ---------------------- -----------
dev_04 2000.0MB 0
dev_09 2000.0MB 937696
table_name index_name indid
------------------------------ ------------------------------ ------
syscomments syscomments 1
sysreferences csysreferences 1
sysreferences ncsysreferences 2
sysreferences nc2sysreferences 3
sysusermessages csysusermessages 1
sysusermessages ncsysusermessages 2
user_bank_detail_u pk_user_bank_detail_u 1
user_bank_detail_u_1 pk_user_bank_detail_u_1 1
user_bank_detail_u_1_2 pk_user_bank_detail_u_1_2 1
user_bank_detail_u_1_2_3 pk_user_bank_detail_u_1_2_3 1
user_bank_detail_u_1_3 pk_user_bank_detail_u_1_3 1
user_bank_detail_u_2 pk_user_bank_detail_u_2 1
user_bank_detail_u_2_3 pk_user_bank_detail_u_1_3 1
user_bank_detail_u_3 pk_user_bank_detail_u_3 1
user_trade i_user_trade_key 1
total_size total_pages free_pages used_pages
---------------------- ----------- ----------- -----------
4000.0MB 2048000 937696 1110304
(return status = 0)
你自己分析一下啦。
zengpan_panpan 2003-02-20
- 打赏
- 举报
你说mid_history_ls比r_history_ls还小，实际上r_history_ls只占用了5358K。那么mid_history_ls应该更小了，所以这个结果看不出问题。
必须还要sp_helpdb的结果。
必须还要sp_helpdb的结果。
rover_sh 2003-02-20
- 打赏
- 举报
当然，公司的数据库没有发生上诉问题的！1，2，3，是在客户的unix的数据库服务器上报出的错误，就是我往中间表插入数据，插入完数据后创建索引后报的错误的，数据当然没有写到mid_history_ls中了，请高手们指点！谢谢
rover_sh 2003-02-20
- 打赏
- 举报
我公司的机器上是下面的结果的：
name rowtotal reserved data index_size unused
-------------------- ----------- --------------- ---------------
--------------- ---------------
r_history_ls 7413 5358 KB 3224 KB
2026 KB 108 KB
(return status = 0)
name rowtotal reserved data index_size unused
-------------------- ----------- --------------- ---------------
--------------- ---------------
r_history_ls 7413 5358 KB 3224 KB
2026 KB 108 KB
(return status = 0)
rover_sh 2003-02-20
- 打赏
- 举报
我没办法再客户那边测试的，我只能在公司试好了才可以去的，而且是银行内部的机器，我轻易不敢试验的！如果是这个表的空间不够了，你现在知道能够怎么解决吗。，谢谢！zengpan_panpan()
zengpan_panpan 2003-02-20
- 打赏
- 举报
sp_helpdb bocdb
go
use bocdb
go
sp_spaceused mid_history_ls
go
运行这些，把结果贴出来看看
go
use bocdb
go
sp_spaceused mid_history_ls
go
运行这些，把结果贴出来看看
rover_sh 2003-02-20
- 打赏
- 举报
2.sort faild:out of space in database 'bocdb'是我在creating a clustered index的时候报的错误，是不是表大的话就必须在批导入数据前创建索引，而不是等导入后再创建索引的？？？我再google搜索到几个关于1。的问题解答，可是没有什么使之性的！
MicroMouse 2003-02-20
- 打赏
- 举报
空间是没问题，但你的“default segment”分配了多少？
