求SQL2000问题 ？恐怖！！！！！！恐怖！！！！！！！！！！！！！！！！！！！！！！！！！！！！

[cqwzhy]

数据爆炸式增长。不知为何?


硬盘好像是做了陈列。电脑是专业服务器但是好像有点慢？
安装了sql 2000以前还正常,一天数据有4,5M 现在业务量一样,但每天有100M~200M
不知为何？请问数据库和硬盘的分区格式有关吗？和陈列的关系大不大？
还是其它的问题？？？？


高手指点。。。。。。。。。。。。头都大了..............

[cqwzhy]

测试发现其中两个表有近10G 的空间但数据量并不大

[csw200201]

[Quote=引用 8 楼 cqwzhy 的回复:]
测试发现其中两个表有近10G 的空间但数据量并不大
[/Quote]

That most likely means that these tables are being frequently changed - resulting in a lot of space held due to new data hitting the table and old data being deleted. That would also mean that your transaction log should see significant actions. Transaction log growth may not be reflected in actual file size increase if there is frequent backup of the log or the database recovery model is set to simple recovery.

You can reclaim empty space by reindexing the entire table (setting free space percentage option in the reindex operation) on a nightly process. If there is no real growth of data, only large amount of churn in data, then a nightly process should clean up the file growth symptom you are experiencing.

[htl258_Tony]

[Quote=引用 9 楼 csw200201 的回复:]
引用 8 楼 cqwzhy 的回复:
测试发现其中两个表有近10G 的空间但数据量并不大


That most likely means that these tables are being frequently changed - resulting in a lot of space held due to new data hitting the table and old da……
[/Quote]
UP，无人使用时维护一下，重建表的索引

[东那个升]

[Quote=引用楼主 cqwzhy 的回复:]
数据爆炸式增长。不知为何?


硬盘好像是做了陈列。电脑是专业服务器但是好像有点慢？
安装了sql 2000以前还正常,一天数据有4,5M 现在业务量一样,但每天有100M~200M
不知为何？请问数据库和硬盘的分区格式有关吗？和陈列的关系大不大？
还是其它的问题？？？？


高手指点。。。。。。。。。。。。头都大了..............
[/Quote]

重建索引。。还有表设计不合理也会浪费空间

[htl258_Tony]

建议用事件探查器跟踪一下SQL，看看数据库到底干了些什么？

[cqwzhy]

应该不会有其它的东西，单位上的数据服务器不让做其它的。。。。

[永生天地]

是增删改操作频繁，日志增长

[cqwzhy]

不是日志。这个专门处理了的。是MDF 文件超大。。每天还在不停的涨。

[永生天地]

用这个查看一下所有表的占用空间情况

if object_id('tb')is not null drop table tb
go
create table tb(
表名 sysname,
记录数 int,
保留空间 nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间   varchar(10))
exec sp_MSForEachTable @command1=N'insert tb exec sp_spaceused ''?'''
select * from tb

[csw200201]

#1楼 already suggested that you use SQL Profiler to look at SQL statements being executed - that may well have been an increase in activity. You won't know unless you look at it.

You can also use sp_spaceused stored procedure to determine which table is growing most and then look at the queries related to this table.

[dla001]

[Quote=引用 6 楼 csw200201 的回复:]
#1楼 already suggested that you use SQL Profiler to look at SQL statements being executed - that may well have been an increase in activity. You won't know unless you look at it.

You can also use sp……
[/Quote]

up