造成mdf文件过大的原因

一般造成mdf文件过大的原因是: 1.索引碎片过多, 2.频繁的数据库操作,如临时/存储过程等的占用大量的空间,而没有及时释放. 3.异常状况下占用的数据库空间没有释放掉..... 1：整理索引碎片   你可能已经创建好了索引，并且所有索引都在工作，但性能却仍然不好，那很可能是产生了索引碎片，你需要进行索引碎片整理。 　　什么是索引碎片?   由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。   有两种类型的索引碎片：内部碎片和外部碎片。   内部碎片：为了有效的利用内存，使内存产生更少的碎片，要对内存分页，内存以页为单位来使用，最后一页往往装不满，于是形成了内部碎片。   外部碎片：为了共享要分段，在段的换入换出时形成外部碎片，比如5K的段换出后，有一个4k的段进来放到原来5k的地方，于是形成1k的外部碎片。   如何知道是否发生了索引碎片?   执行下面的SQL语句就知道了(下面的语句可以在SQL Server 2005及后续版本中运行，用你的数据库名替换掉这里的AdventureWorks)： 　SELECT object_name(dt.object_id) Tablename,si.name   IndexName,dt.avg_fragmentation_in_percent AS   ExternalFragmentation,dt.avg_page_space_used_in_percent AS   InternalFragmentation   FROM   (   SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent   FROM sys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'   )   WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id   AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10   AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC   执行后显示AdventureWorks数据库的索引碎片信息。   图 3 索引碎片信息   使用下面的结果，你就可以找出哪里发生了索引碎片：   1)ExternalFragmentation的值>10表示对应的索引发生了外部碎片;   2)InternalFragmentation的值<75表示对应的索引发生了内部碎片。   如何整理索引碎片?   有两种整理索引碎片的方法：   1)重组有碎片的索引：执行下面的命令   ALTER INDEX ALL ON TableName REORGANIZE   2)重建索引：执行下面的命令   ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)   也可以使用索引名代替这里的“ALL”关键字重组或重建单个索引，也可以使用SQL Server管理工作台进行索引碎片的整理。 图 4 使用SQL Server管理工作台整理索引碎片 什么时候用重组，什么时候用重建呢?   当对应索引的外部碎片值介于10-15之间，内部碎片值介于60-75之间时使用重组，其它情况就应该使用重建。   值得注意的是重建索引时，索引对应的表会被锁定，但重组不会锁表，因此在生产系统中，对大表重建索引要慎重，因为在大表上创建索引可能会花几个小时，幸运的是，从SQL Server 2005开始，微软提出了一个解决办法，在重建索引时，将ONLINE选项设置为ON，这样可以保证重建索引时表仍然可以正常使用。   虽然索引可以提高查询速度，但如果你的数据库是一个事务型数据库，大多数时候都是更新操作，更新数据也就意味着要更新索引，这个时候就要兼顾查询和更新操作了，因为在OLTP数据库表上创建过多的索引会降低整体数据库性能。   我给大家一个建议：如果你的数据库是事务型的，平均每个表上不能超过5个索引，如果你的数据库是数据仓库型，平均每个表可以创建10个索引都没问题。 整理索引碎片 use database --对指定的整个数据库所有表进行重新组织索引 set nocount on --使用游标重新组织指定库中的索引,消除索引碎片 --R_T层游标取出当前数据库所有表 declare R_T cursor for select name from sys.tables declare @T varchar(50) open r_t fetch next from r_t into @t while @@fetch_status=0 begin --R_index游标判断指定表索引碎片情况并优化 declare R_Index cursor for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t   join sys.indexes i on i.object_id=t.object_id   join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s   on s.object_id=i.object_id and s.index_id=i.index_id declare @TName varchar(50),@IName varchar(50),@avg int,@str varchar(500) open r_index fetch next from r_index into @TName,@Iname,@avg while @@fetch_status=0 begin   if @avg>=30  --如果碎片大于30,重建索引   begin   set @str='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' rebuild'   end   else  --如果碎片小于30,重新组织索引   begin   set @STR='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' reorganize'   end   print @str   exec (@str)  --执行   fetch next from r_index into @TName,@Iname,@avg end --结束r_index游标 close r_index deallocate r_index fetch next from r_t into @t end --结束R_T游标 close r_t deallocate r_t set nocount off 查询sql数据库中表占用的空间大小 最近在给一家客户做系统维护时，需要查看数据库表的大小，相关的sql如下： 1. exec sp_spaceused '表名'          --（SQL统计数据，大量事务操作后可能不准） 2. exec sp_spaceused '表名', true       --(准确的表空间大小，但可能会花些统计时间） 3. exec sp_spaceused                   -- (数据库大小查询） 4. exec sp_MSforeachtable "exec sp_spaceused '?'"     --(所有用户表空间表小，SQL统计数据，，大量事务操作后可能不准） 5. exec sp_MSforeachtable "exec sp_spaceused '?',true"    --(所有用户表空间表小，大数据库慎用） create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20)) exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'" select * from #t drop table #t