请教SQLSERVER数据库文件达到20多G原因

[ChengKing]

客户数据库达到28G，而真正的数据占用只有600M(新建数据库，从大数据库导入的数据；这里指数据文件dbf)。
请问如下三个问题：
1. 数据库自动增长的原因是什么？为什么增的这么大。SQLSERVER以什么依据来自动增加数据库？
2. 有没有好的办法把此数据库缩小一下。数据库收缩没有用。
3. 怎样避免数据库文件增加得如此快？

数据库版本为SQL SERVER 2000。在SQL SERVER2005中也存在同样的问题。

请知道朋友说一下经验。

谢谢！

[FlySQL]

主要是因为日志文件增长吧，使用日志收缩的方法可以让数据库日志收缩到某个固定值
不过有得必有失，日志不全，有时也是灾难性的

[htl258_Tony]

楼主已指定了是dbf，说明跟日志文件无关，这个等KG和猫来解释，帮顶了。

[htl258_Tony]

楼主是不是打错字了，不是dbf,应该是mdf文件吧？

[feixianxxx]

2. 有没有好的办法把此数据库缩小一下。数据库收缩没有用。
==>
我猜想那是因为运行 DBCC SHRINKDATABASE 时候指定 NOTRUNCATE
（
NOTRUNCATE
通过将已分配的页从文件末尾移动到文件前面的未分配页来压缩数据文件中的数据。target_percent 是可选参数。

文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。因此，指定 NOTRUNCATE 时，数据库看起来未收缩。

NOTRUNCATE 只适用于数据文件。日志文件不受影响。
）


3. 怎样避免数据库文件增加得如此快？
=======》
数据库文件中的数据文件没办法减慢 你本来业务数据就这么多 除非你及时做好“归档”工作或者定期删除不必要的数据。
如果是日志文件 可以通过日志备份后收缩文件DBCC SHRINKFILE 来收缩日志文件

[feixianxxx]

1. 数据库自动增长的原因是什么？为什么增的这么大。SQLSERVER以什么依据来自动增加数据库？

===》

建立数据库的时候指定了自动增长 FILEGROWTH 属性 。。。
这个数据数据增大 那是因为你数据插入多了 至于日志文件的话 那是因为你进行的操作很频繁。。

[dawugui]

[Quote=引用楼主 chengking 的回复:]
客户数据库达到28G，而真正的数据占用只有600M(新建数据库，从大数据库导入的数据；这里指数据文件dbf)。
请问如下三个问题：
1. 数据库自动增长的原因是什么？为什么增的这么大。SQLSERVER以什么依据来自动增加数据库？
2. 有没有好的办法把此数据库缩小一下。数据库收缩没有用。
3. 怎样避免数据库文件增加得如此快？

数据库版本为SQL SERVER 2000。在SQL ……
[/Quote]你把日志清除了就是了./*
标题：清除日志和收缩日志
作者：爱新觉罗·毓华
时间：2008-07-14
地点：新疆乌鲁木齐
资料来源：深圳外经贸局年检数据库和CSDN会员adis789咨询sql server日志为啥会增长很快，
有一个程序每5秒钟写数据一次，然后数据库日志经常增长很快，怎么避免这样的事情发生呢？
怎么能让sql server日志增长不要这么快，数据文件才100M日志文件居然1G多。
*/

--清除日志：
DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT
USE     szwzcheck             -- 要操作的数据库名
SELECT  @LogicalFileName = 'szwzcheck_Log',  -- 日志文件名
@MaxMinutes = 10,               -- Limit on time allowed to wrap log.
        @NewSize = 20                  -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size 
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans(DummyColumn char (8000) not null)
DECLARE @Counter   INT,
        @StartTime DATETIME,
        @TruncLog  VARCHAR(255)
SELECT  @StartTime = GETDATE(),
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time 
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = 
@LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize  
  BEGIN -- Outer loop.
    SELECT @Counter = 0
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
      BEGIN -- update
        INSERT DummyTrans VALUES ('Fill Log')  
        DELETE DummyTrans
        SELECT @Counter = @Counter + 1
      END   
    EXEC (@TruncLog)  
  END   
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles 
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
 
--把szwzcheck换成你数据库的名字即可，在查询分析器里面运行。

--收缩日志：企业管理器--所有任务--收缩数据库--文件--选日志文件收缩

[永生天地]

记得有一次给你个2000万的表重建了索引，然后数据库就从1G涨到30多G，然后通过各种收缩，最后也有3G多

[synhy]

是不是有很多char(100)这种很长的字段？
或虽然用了 varchar(100) 但是导入的原始数据没有进行 rtrim() ？

建议使用varchar，同时对文本进行 rtrim

[Garnett_KG]

楼主你先回答以下问题:

1). 28G 是 数据文件大小 还是 日志大小

2). "数据占用只有600M" 这个判断是怎么来的？

3). 数据导入过程是怎样的？数据源是什么？

[dla001]

index太多会导致数据少，但使用空间很大
见过乱建index的 10G左右数据 100多G的index 真是无言了

物理化view也会占用空间

选择正确的存储类型也很重要，不是一定要用char（nchar）。

[lostwing2005]

自动增长是在数据库属性里设置的，默认为10%，可以根据你们实际情况进行修改。

数据库在完全恢复模式下，重建索引会产生大量的日志。
在每次重建索引后，可以把数据库改成简单恢复模式，然后收缩日志文件，再改回完全恢复模式。

[appleller]

先确定是日志文件大还是数据文件大
如果是日志文件大，直接收缩就好了，很简单
如果是数据文件大，一般直接收缩不了多大空间的，
可以看看哪些表比较大，
if exists (select * from dbo.sysobjects where id = object_id([dbo].[tablespace]) and objectproperty(id, nisusertable) = 1)
drop table [dbo].[tablespace]
go

create table tablespace
(
tablename varchar(20),
rowscount char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
go

declare @sql varchar(500)
declare @tablename varchar(20)

declare cursor1 cursor

for
select name from sysobjects where xtype='U'

open Cursor1
fetch next from Cursor1 into @TableName

while @@fetch_status = 0
begin
set @sql = 'insert into TableSpace '
set @sql = @sql + ' exec sp_spaceused '''+ @TableName + ''' '
exec (@sql)
fetch next from Cursor1 into @TableName
end
close Cursor1
deallocate Cursor1
go

--显示结果
select * from tablespace
--order by tablename
--order by tablename asc --按表名称，用于统计表
order by rowscount desc --按行数量，用于查看表行数
--order by reserved desc, data desc --按占用空间
--order by index_size desc, reserved desc --按索引空间查看
go

drop table TableSpace



如果有不用的临时表或数据，可以手动清除

然后，可以对空间较大的表重建索引或者整个数据库表重建索引，会缩小不用的空间
use database_name

declare @table_id int

set @table_id=object_id('')

dbcc showcontig(@table_id)

dbcc dbreindex('database_name.dbo.Employee','',90)


然后再收缩数据文件。这样收缩率较高。

最终达到日志文件与数据文件都最大收缩的目的。

[menggang9801]

9楼正解

[bycz123456]

嗯。。。

[jwwyqs]

收缩数据库文件和日志文件

[dichun]

1. 数据库自动增长的原因是什么？为什么增的这么大。SQLSERVER以什么依据来自动增加数据库？
1。先看看是数据库文件大还是日志文件大，如果是日志可以把数据库设置为简单模式，但承担一些风险；
2。对数据进行全备份，进行事务截断；
增加模式默认一般是数据库的全部空间的百分比；

2. 有没有好的办法把此数据库缩小一下。数据库收缩没有用。
对数据进行全备份，进行事务截断；对数据库进行收缩；

3. 怎样避免数据库文件增加得如此快？
避免大的事务处理，如大量插入、删除、修改数据；

[csw200201]

I think you must have made a typo. You must have meant ldf that is taking up all the space not MDF. If MDF, it doesn't make sense that the file size would expand without actual data expanding as well. Unless what you meant is that you have used sp_spaceused to determine that data is taking only 600 MB and the rest being taken up by indices, then I am at a complete loss how it can occur.

However, if my suspicion is right that you made a typo, then basically the issue comes down to a failure to do transaction log backups - that results in log being continually expanded to accomodate new transactions. Simple fix would be to conduct log backups regularly and if necessary, truncate log as the end of the log backup job. Once your log is cleaned up, you can shrink database file successfully.

[饮水需思源]

1，在查询分析器中执行：dump transaction 数据库名 with no_log
2，在企业管理器中收缩对应数据库

[weiki516]

(⊙o⊙)… 关注 关键是弄清楚是哪个文件大了