ASE15创建非聚簇索引时报数据缓冲内存不够

---------------------------------------------------------------------------

---- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；

---- 转载务必注明原始出处 : http://blog.youkuaiyun.com/andkylee

--- 2010-07-13 17:43:27

---- 关键字：  nonclustered index default data cache insufficient 1526

                   数据缓存不足 非聚簇索引

----------------------------------------------------------------------------

今天下午在solaris10 ASE15.0.3平台上批量创建非聚簇索引时，报下面的错误：

Msg 1526, Level 17, State 5: Server 'SYB_NFJD_TEST', Procedure 'sp_helpdb', Line 674: Sort failure. The configure size for data cache (default data cache) is not sufficient to handle the sort buffers (128) requested. Please reconfigure the data cache or sort buffers and try the command again. (return status = -7)

 

用命令:sp_helpcache "default data cache"，发现高速数据缓冲大小为：8M，此为默认值。

执行命令将default data cache扩充至100M，sp_cacheconfig "default data cache","100m"

然后再重新执行一遍非聚簇索引的创建过程，成功完成了!

 

在error message & troubleshooting 中没有找到错误号1526的解释以及解决办法。

目前暂时没有想明白，为什么在创建非聚簇索引的时候需要用default data cache,而且默认数据缓存不足还会出错。难道default data cache要缓存某一层次的索引的页数据？

 

 

------------------------------------------------------------------------------------------------

以下关联一篇网上搜索的也是关于error：1526的信息

The failure is most likely happening on the remote server where the actual
physical table lives. It's certainly possible to create indexes on proxy
tables, but what really happens is that the physical index is created on the
remote server. Try logging in to the remote server where the actual table
resides. Check the memory settings on the remote server.

> Hi,
> I am trying to create a non clustered index on an "existing
> table"(proxy table). But it keeps failing with the error,
>
> Sort failure. The configure size for data cache (default
> data cache) is not
> sufficient to handle the sort buffers (30008) requested.
> Please reconfigure the
> data cache or sort buffers and try the command again.
>
> The data cache is configured 2 Gig, 800MB for 8K pool
> I have this configuration enough for most of the things.
>
> How can I make the non clustered index.

 

总结一下： 在建立非聚簇索引的时候，都涉及到default data cache数据缓存的使用。不管是在基本表还是在代理表上创建非聚簇索引，都会在物理表所在的ASE服务器上使用数据缓存。如果数据缓存不足，则会报1526的错误信息。解决办法为用过程sp_cacheconfig增加default data cache即可。