紧急处理：我在用一条SQL语句执行时，出现如下提示：(100分)

紧急处理：我在用一条SQL语句执行时，出现如下提示：
select error:Cannot deallocate extent 680,database 2. Object id 0,
index id 0,status 0 in extent does not match object id -1203,index
id 0,status 0 in object being deallocated. Run DBCC CHECKALLOC.
请问各位该怎么处理呢？不胜感激，在线等待。

把代码帖出来吧

我的代码是在PB里写的DW，如下：
SELECT product_code.prod_id,
product_code.prod_name,
product_code.prod_stand,
product_code.prod_unit,
item_name = (
( case
when :choose_para = 0 then '按日期汇总'
when :choose_para = 1 then (select distinct custom_code.cust_name from custom_code where custom_code.cust_id = :assi_id )
when :choose_para = 2 then (select distinct dept_code.dept_name from dept_code where dept_code.dept_id = :assi_id)
when :choose_para = 3 then :assi_id
when :choose_para = 4 then :assi_id
end )
),
in_account = ( select sum(prod_in) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= :begin_date) and
(prod_inout.prod_date <= :end_date) and
prod_inout.prod_inout in ('1','3') and
( case
when :choose_para = 0 then :assi_id
when :choose_para = 1 then prod_inout.cust_id
when :choose_para = 2 then prod_inout.dept_id
when :choose_para = 3 then prod_inout.inout_staff
when :choose_para = 4 then prod_inout.prod_memo
end ) like '%'+:assi_id+'%'
) ),

in_money = ( select sum(prod_piece) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= :begin_date) and
(prod_inout.prod_date <= :end_date) and
prod_inout.prod_inout in ('1','3') and
( case
when :choose_para = 0 then :assi_id
when :choose_para = 1 then prod_inout.cust_id
when :choose_para = 2 then prod_inout.dept_id
when :choose_para = 3 then prod_inout.inout_staff
when :choose_para = 4 then prod_inout.prod_memo
end ) like '%'+:assi_id+'%'
) ),
out_account = ( select sum(prod_in) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= :begin_date) and
(prod_inout.prod_date <= :end_date) and
prod_inout.prod_inout in ('2','4') and
( case
when :choose_para = 0 then :assi_id
when :choose_para = 1 then prod_inout.cust_id
when :choose_para = 2 then prod_inout.dept_id
when :choose_para = 3 then prod_inout.inout_staff
when :choose_para = 4 then prod_inout.prod_memo
end ) like '%'+:assi_id+'%'
) ),
out_imoney = ( select sum(prod_piece) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= :begin_date) and
(prod_inout.prod_date <= :end_date) and
prod_inout.prod_inout in ('2','4') and
( case
when :choose_para = 0 then :assi_id
when :choose_para = 1 then prod_inout.cust_id
when :choose_para = 2 then prod_inout.dept_id
when :choose_para = 3 then prod_inout.inout_staff
when :choose_para = 4 then prod_inout.prod_memo
end ) like '%'+:assi_id+'%'
) ),
type_name = ( select product_type.prod_tname from product_type
where product_type.prod_type = substring(product_code.prod_id,1,2) ),
type_nameid = ( select product_type.prod_tname from product_type
where product_type.prod_type = substring(product_code.prod_id,2,1) )

FROM product_code

对不起，我没搞过pb,帮不了你了

这不是VB的错误
而是SQL SERVER发出的错误。在SQL环境中执行也是出现这种错误。

他叫你执行 DBCC CHECKALLOC 你就执行 DBCC CHECKALLOC 吧

我执行了DBCC CHECKALLOC，但问题还是存在，有谁知道问题的解决方法吗？

数据库出问题了，不是程序的文件。
或许是空间用完了。

对不起，我没有做过。

执行其他语句有问题吗？如果问题依然存在，是数据库出问题了

其他语句没有问题。

你没有数据库提供的话，起码也要作一些注释啊，不然别人怎么看。

在查询分析器中，要给每个参数赋正确的值才行。

使用tran-sql 语句:
dbcc CHECKALLOC('yourdatabasename',REPAIR_REBUILD)
语句修复数据库，最好先作备份.

楼上兄弟：dbcc CHECKALLOC('yourdatabasename',REPAIR_REBUILD)语句不能执行，说明
一下，我的数据库是MS SQL6.5。谁能帮我。
SQL 语句如下：
declare @choose_para int,@assi_id char(5),@begin_date datetime,@end_date datetime
select @choose_para=2 //查询的条件类别
select @assi_id='pa033' //产品代码
select @begin_date='2002/1/2' //开始日期
select @end_date='2002/4/2' //结束日期
SELECT product_code.prod_id,
product_code.prod_name,
product_code.prod_stand,
product_code.prod_unit,
item_name = (
( case
when @choose_para = 0 then '按日期汇总'
when @choose_para = 1 then (select distinct custom_code.cust_name from custom_code where custom_code.cust_id = @assi_id )
when @choose_para = 2 then (select distinct dept_code.dept_name from dept_code where dept_code.dept_id = @assi_id)
when @choose_para = 3 then @assi_id
when @choose_para = 4 then @assi_id
end )
),
in_account = ( select sum(prod_in) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= @begin_date) and
(prod_inout.prod_date <= @end_date) and
prod_inout.prod_inout in ('1','3') and
( case
when @choose_para = 0 then @assi_id
when @choose_para = 1 then prod_inout.cust_id
when @choose_para = 2 then prod_inout.dept_id
when @choose_para = 3 then prod_inout.inout_staff
when @choose_para = 4 then prod_inout.prod_memo
end ) like '%'+@assi_id+'%'
) ),

in_money = ( select sum(prod_piece) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= @begin_date) and
(prod_inout.prod_date <= @end_date) and
prod_inout.prod_inout in ('1','3') and
( case
when @choose_para = 0 then @assi_id
when @choose_para = 1 then prod_inout.cust_id
when @choose_para = 2 then prod_inout.dept_id
when @choose_para = 3 then prod_inout.inout_staff
when @choose_para = 4 then prod_inout.prod_memo
end ) like '%'+@assi_id+'%'
) ),
out_account = ( select sum(prod_in) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= @begin_date) and
(prod_inout.prod_date <= @end_date) and
prod_inout.prod_inout in ('2','4') and
( case
when @choose_para = 0 then @assi_id
when @choose_para = 1 then prod_inout.cust_id
when @choose_para = 2 then prod_inout.dept_id
when @choose_para = 3 then prod_inout.inout_staff
when @choose_para = 4 then prod_inout.prod_memo
end ) like '%'+@assi_id+'%'
) ),
out_imoney = ( select sum(prod_piece) from prod_sub
where prod_sub.prod_id = product_code.prod_id and
prod_sub.prod_number in ( select prod_inout.prod_number
from prod_inout
where (prod_inout.prod_date >= @begin_date) and
(prod_inout.prod_date <= @end_date) and
prod_inout.prod_inout in ('2','4') and
( case
when @choose_para = 0 then @assi_id
when @choose_para = 1 then prod_inout.cust_id
when @choose_para = 2 then prod_inout.dept_id
when @choose_para = 3 then prod_inout.inout_staff
when @choose_para = 4 then prod_inout.prod_memo
end ) like '%'+@assi_id+'%'
) ),
type_name = ( select product_type.prod_tname from product_type
where product_type.prod_type = substring(product_code.prod_id,1,2) ),
type_nameid = ( select product_type.prod_tname from product_type
where product_type.prod_type = substring(product_code.prod_id,2,1) )

FROM product_code

出现错误如下：
Msg 1105, Level 17, State 1
Can't allocate space for object '-435' in database 'tempdb' because the 'system'
segment is full. If you ran out of space in Syslogs, dump the transaction log.
Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of
the segment.
Msg 1510, Level 17, State 2
Sort failed: Out of space or locks in database 'tempdb'
Msg 1108, Level 21, State 1
Cannot deallocate extent 680, database 2. Object id 0, index id 0, status 0
in extent does not match object id -435, index id 0, status 0 in object being
deallocated. Run DBCC CHECKALLOC.

tempdb数据库满了，我记得学sql server 时说sql 6.5的 tempdb
数据库有很大的隐患，为什么不升级，用的是正版吗?

转载自:http://www.swynk.com/friends/vartanyan/DBOptim65.asp

Some tips about database optimization for SQL Server 6.5


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



Tempdb database
SQL Server 6.5 installation put tempdb database on the master database
device. The size of the tempdb database is 2MB by default. Usually, it
is not enough. So, you should increase the size of the tempdb database.
To increase the size of the tempdb database, you can expand master
device and then expand tempdb database, or create new device and then
expand tempdb database on this new device. You can also move tempdb
database on its own database device to simplify administering and
monitoring.

Here is step by step description:

1. Create new database device with the appropriate size.
2. Uncheck "Default device" option for the master database device.
3. Set "Default device" option for the new device.
4. Put tempdb database into RAM from the Enterprise Manager or
sp_configure system stored procedure.
5. Stop and start MSSQLServer service.
6. Put tempdb database back on the hard disk from the Enterprise Manager
or sp_configure system stored procedure.
7. Stop and start MSSQLServer service.
8. Set whichever device you want to be default.

If you want to increase a speed of your queries (if these queries contain
subqueries, or GROUP BY, or ORDER BY clause), you can put tempdb database
into RAM.
By the way, tempdb in RAM is no longer supported in SQL Server 7.0.

Using segments
Segment is a named collection of disk pieces. It is a subset of one
or more database devices that is available to a particular database.
Each database can use up to 32 segment.
You can increase SQL Server 6.5 performance by creation specific segments
for specific tables or indexes.

By the way, Microsoft recommends to use hardware-based RAID or Windows NT
software-based RAID instead of using segments.

There are three segments in SQL Server 6.5 (by default):

system segment
logsegment
default segment

The system segment is used to store the system tables.
The logsegment is used to store transaction log.
The default segment is used to store all other database objects,
unless you create additional segment and put data into it by
using sp_placeobject system stored procedure or by CREATE TABLE
statement.

Microsoft recommends to use segments for
(this is from SQL Server Books Online):

Placing a table on one physical device and its nonclustered
indexes on a different physical device.
Splitting a large, heavily-used table across database devices
on two separate disk controllers.
Storing data for text and image columns on separate physical devices.
Placing a transaction log on a separate device so it can be dumped.

Create separate device for the transaction log
In SQL Server 6.5, any database (except the master database) can
span multiple devices. If you want to ensure recoverability and
reduce contention, you should place the transaction log on a separate
device.

Put the log device on a different physical disk. So if one disk
crashes, you have the database, or the log plus a recent backup,
and in either case, you can recover.

Place transaction log on the faster hard drive
Read about "Microsoft SQL Server Optimization and Tuning" from msdn.
It's therefrom:

"
Windows NT File System Selection

Microsoft's internal testing has shown that there is very little
difference in SQL Server performance between New Technology file
systems (NTFS) and file allocation table (FAT) file systems.
In general, NTFS is faster than FAT for reads, and slightly slower
for writes (because it performs transaction logging for
recoverability). The SQL Server TPC-B benchmarks published by
COMPAQ hardware used NTFS for the database devices and FAT for
the log, because logging is write-intensive.
"

So, transaction log is more write-intensive than data device, and
transaction log should be placed on the faster hard drive.

Raw partition
You can create a database device on the raw partition. It can increase
the speed of your queries and INSERT/UPDATE/DELETE operations more than
on 20 percents.

Raw partition has several limitations:

Each raw partition can contain only one device.
You cannot use file system operations such as copy, move
and delete with raw partitions.
The Windows NT Backup utility cannot be used to backup the
devices located on raw partitions.
Some file system services such as bad block replacement are not
available with raw partitions.

Calculate the size of the database
Because database in SQL Server 6.5 cannot automatically increased,
you should calculate estimating database size.

See the following link to decide how it can be made:
"Special Edition, Using SQL Server 6.5 Second Edition"
by Stephen Wynkoop, published by Que Corporation
Optimizing Performance

It's difficult to make some general recommendations about the size
of the transaction log, so start with 20 percent of the database size
and gather the information about log growing.

是的，正版，这里有MCDBA吗？或者微软工作的数据库管理员有吗？

是数据库临时存储空间满了，你的语句查询结果或计算量应该很大吗，检查一下是否是
少写的条件，比如From 了两个表，但没有做两个表的关联，如果关联等都没问题，你就将
TempDb的空间加大吧，Sql Server默认的临时存储空间只有2M，不够用

谢谢 xeen，问题找出来了，是sp_configure里面的tempdb in ram (MB)选项里数值为0，
后来加到50就一切正常了。谢谢了。