sql server mdf文件数据过大的解决办法

EXEC sp_spaceused @updateusage = N'TRUE';

 

原文链接：https://jingyan.baidu.com/album/215817f7a1c8fb1eda1423ca.html?picindex=6

https://blog.csdn.net/fucong920618717/article/details/89371392

背景1：TCX_1807现场数据库的mdf文件有90+G，ldf文件倒是不到只有几兆，我怀疑是上次执行了历史数据库后删除了很多数据，而这些数据所占的空间并不会自动缩小，因而导致mdf文件过大。

背景2：TCX_1909现场数据库的mdf文件有10+G，ldf文件也不是很多，也执行了数据历史化。对现场数据库备份后bak文件有3+G，还原到本地后删除了大表数据，收缩了数据库，再次备份本地数据库bak文件和mdf文件竟然有4+G，mdf文件减少可以理解，bak文件比在现场备份的还大就不理解了？？？

 

分析问题时用到的sql脚本

查询数据库的大小的系统存储过程sp_spaceused（会返回两条数据）：

为了保证查询结果的实时性，推荐使用 @updateusage 参数来确保统计数据是最新的：

EXEC sp_spaceused @updateusage = N'TRUE';

 

具体查询某个表的大小：

EXEC sp_spaceused 't_dd_qm_quality_collection_final'

 

 

 

查询数据库中所有表的大小的SQL语句（2种）：

第一种：通过表变量

declare @table_spaceused table (name nvarchar(100) ,rows int ,reserved nvarchar(100) ,data nvarchar(100) ,index_size nvarchar(100) ,unused nvarchar(100) ) 
insert into @table_spaceused (name,rows,reserved,data,index_size,unused ) exec sp_MSforeachtable @command1='exec sp_spaceused ''?''' 
select * from @table_spaceused order  by  rows desc

 

 第二种：通过临时表

IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL DROP TABLE #TablesSizes
CREATE TABLE #TablesSizes(TableName sysname,Rows BIGINT,reserved VARCHAR(100),data VARCHAR(100),index_size VARCHAR(100),unused VARCHAR(100))
DECLARE @sql VARCHAR(MAX)
SELECT  @sql = COALESCE(@sql, '') + 'INSERT INTO #TablesSizes execute sp_spaceused ''' + QUOTENAME(TABLE_SCHEMA, '[]') + '.'  + QUOTENAME(Table_Name, '[]') + ''''
FROM    INFORMATION_SCHEMA.TABLES WHERE   TABLE_TYPE = 'BASE TABLE'
PRINT ( @SQL )
EXECUTE (@SQL)
SELECT *  FROM    #TablesSizes ORDER BY Rows DESC

字段含义：

• name：表名
• rows：行数
• reserved：数据库为该表分配的空间
• data：数据实际使用的空间，reserved肯定>=data
• index_size：索引使用的空间
• unused：为数据库中的对象保留但尚未使用的空间总量，大致等于reserved - data - index_size的值

然后根据表中的业务选择性删除无用的数据，truncate table 表名 来清空表中无用的数据

 

重新组织索引的SQL语句：

 

ALTER INDEX [PK__t_dd_qm___3213E83F3CC4D561] ON [dbo].[t_dd_qm_quality_collection_final] REORGANIZE  WITH ( LOB_COMPACTION = ON )

重新生成索引的SQL语句：

DBCC DBREINDEX (t_dd_qm_quality_collection_final, '', 90)//后续版本的 Microsoft SQL Server 将删除该功能。 请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。请改用 ALTER INDEX。

ALTER INDEX [PK__t_dd_qm___3213E83F3CC4D561] ON [dbo].[t_dd_qm_quality_collection_final] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

 

为某数据库所有表重新生成索引的SQL语句：

DECLARE @name varchar(100)
DECLARE authors_cursor CURSOR FOR Select [name]   from sysobjects where xtype='u' order by id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor  INTO @name
WHILE @@FETCH_STATUS = 0 
BEGIN    
   DBCC DBREINDEX (@name, '', 90)
   FETCH NEXT FROM authors_cursor  
   INTO @name 
END
close authors_cursor
deallocate authors_cursor

 

收缩数据库的SQL语句：

ALTER DATABASE WLZhuJianMes SET RECOVERY SIMPLE WITH NO_WAIT;  
ALTER DATABASE WLZhuJianMes SET RECOVERY SIMPLE;  

DBCC SHRINKFILE (N'WLZhuJianMes_log' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'WLZhuJianMes' , 0, TRUNCATEONLY)

ALTER DATABASE WLZhuJianMes SET RECOVERY FULL WITH NO_WAIT;   
ALTER DATABASE WLZhuJianMes SET RECOVERY FULL;  

 

查看索引碎片信息：DBCC SHOWCONTIG