添加自动扩展时调整stack size的大小
在为数据库配置自动扩展时，提示需要增加参数：stack size的大小。
执行下面的SQL语句，使得用户数据库pmdb的default段的阈值为：1000m
sp_dbextend 'set','threshold',pmdb,'default','1000m'
go
但是返回下面的错误：
Msg 3626, Level 17, State 4:
Server 'syb1503', Procedure 'sp_exec_SQL', Line 49:
The transaction was aborted because it used too much stack space. Either use
sp_configure to increase the stack size, or break the query into smaller pieces.
spid: 27, suid: 1, hostname: LIUZHENFU, application name: isql
Msg 19206, Level 16, State 1:
Server 'syb1503', Procedure 'sp_exec_SQL', Line 53:
sp_dbxt_ins_db_seginfo: Execute immediate SQL failed. SQL statement is: insert
#syssegments(dbname, inserted_by, segment, name, status) select
'pmdb','sp_dbxt_set_extend_db', segment, name, status from pmdb.dbo.syssegments
(return status = 1)
那就按照提示做吧。 将stack size参数的大小配置为:130KB
sp_configure "stack size",130000
go
此参数：stack size是静态参数，需要重启ASE服务器。
重启后，再次执行为default设置阈值的命令，成功！
use pmdb
go
sp_dbextend 'set','threshold',pmdb,'default','1000m'
go
1> use pmdb
2> go
1> sp_dbextend 'set','threshold',pmdb,'default','1000m'
2> go
Adding threshold for segment 'default' at '64000' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)
1> sp_dbextend 'listfull','database',pmdb
2> go
name segment item value status comment
----------- ------- ------ ----- ------- -------------------------
server-wide (n/a) (n/a) (n/a) enabled set='Jan 13 2011 3:21PM'
default (all) growby 10% enabled set='Jan 13 2011 3:21PM'
(1 row affected)
segment name free pages free pages (KB) threshold procedure
status
-------------- ------------ ----------------- ---------------------
-------------------
default 256000 1024000 sp_dbxt_extend_db
enabled
logsegment 70360 281440 sp_thresholdaction
last-chance,enabled
Msg 3606, Level 16, State 0:
Server 'syb1503', Procedure 'sp_dbxt_help_threshold', Line 142:
Arithmetic overflow occurred.
(return status = -6)
关于错误3626，官方文档的解释如下：
At start-up, Adaptive Server allocates one stack for each configured user connection. These stacks are in contiguous areas of memory, with a guard area at the end of each stack. Adaptive Server periodically checks to determine whether the stack point for a user connection has entered the stack guard area adjacent to that connection's stack. Error 3626 occurs when this has happened.
Because Error 3626 occurs before any stack corruption, Adaptive Server does not shut down. Instead, it aborts the user process and rolls back any transactions that existed for that process.
An Adaptive Server process usually encounters this error for one of two reasons:
-
The stack size is too small. The information placed on the stack is valid but its total size exceeds the specified stack size. This is often caused by a single, complex query which requires more than the average amount of stack space. Long where clauses, long select lists, and deeply nested stored procedures can contribute to this type of stack overflow and Error 3626.
-
An incorrectly handled error results in recursive calls which eventually fill the stack, regardless of its size.
If this error occurs frequently, there is increased risk a process may overflow its stack area and corrupt another process' stack. This situation will cause Adaptive Server to shutdown with the error:
kernel: *** Stack guardword corrupted.
See Stack Guardword Error for more information.