查看数据库表的可释放空间大小，查询语句如下：

SELECT 　top 20　 db_name() as DbName,

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

-- CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,

CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00/1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间GB,

-- SUM(a.used_pages) * 8 AS UsedSpaceKB,

CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceGB,

-- (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceGB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 0

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

UnusedSpaceGB desc

对未使用空间较大的表进行重建索引，语句如下：

alter table dbo.表名 rebuild;(不建议大规模使用，影响业务)

--找到要重建的索引的表

SELECT top 20

t.NAME AS TableName,

CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceGB

--临时表

into #table1

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 0

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

UnusedSpaceGB desc

--把选出的表写进变量里

DECLARE @ColumnData NVARCHAR(MAX);

SELECT @ColumnData = STRING_AGG(CAST(TableName AS NVARCHAR(MAX)), ''',''')

FROM #table1;

set @ColumnData=''''+ @ColumnData + ''''

SELECT @ColumnData

--找到索引

SELECT

TableName = t.name,

IndexName = ind.name,

ind.type_desc,

ind.is_unique,

ind.is_primary_key

into #table

FROM

sys.indexes ind

INNER JOIN sys.tables t ON ind.object_id = t.object_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.name IN ('','')

--t.name IN (SELECT value FROM STRING_SPLIT(@ColumnData, ','))

--in 实际查询的是整个整体。

and ind.name is not null

ORDER BY

ind.is_primary_key DESC

--打印出sql语句

DECLARE @TableName NVARCHAR(MAX);

DECLARE @IndexName NVARCHAR(MAX);

DECLARE @sql NVARCHAR(MAX);

DECLARE tableCursor1 CURSOR FOR

SELECT TableName, IndexName FROM #table;

-- 创建临时表来存储@sql语句

CREATE TABLE #TempSQL (

SQLStatement NVARCHAR(MAX)

);

OPEN tableCursor1;

FETCH NEXT FROM tableCursor1 INTO @TableName, @IndexName;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD with (online=on)';

-- 插入@sql语句到临时表中

INSERT INTO #TempSQL (SQLStatement) VALUES (@sql);

--EXEC sp_executesql @sql；

FETCH NEXT FROM tableCursor1 INTO @TableName, @IndexName;

END

CLOSE tableCursor1;

DEALLOCATE tableCursor1;

-- 查询临时表中的内容

SELECT * FROM #TempSQL;

drop table #table1

drop table #table

drop table #TempSQL

---根据索引碎片率查询

SELECT

OBJECT_NAME(ips.object_id) AS TableName,

i.name AS IndexName,

ips.avg_fragmentation_in_percent AS FragmentationRate,

ips.index_type_desc as IndexType,

ips.page_count AS PageCount,

CONCAT('ALTER INDEX ', i.name,' ON ',OBJECT_NAME(ips.object_id), ' REBUILD with (online=on);') as 索引执行语句

FROM

sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, null) ips

JOIN

sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id

WHERE

ips.avg_fragmentation_in_percent > 30and i.name is not null -- 只查询有碎片化的索引

ORDER BY

ips.avg_fragmentation_in_percent DESC;

---查找大表

SELECT

TableName = obj.name,

TotalRows = prt.rows,

[SpaceUsedcGB] = SUM(alloc.used_pages)*8/1024/1024

FROM sys.objects obj

JOIN sys.indexes idx on obj.object_id = idx.object_id

JOIN sys.partitions prt on obj.object_id = prt.object_id

JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id

WHERE

obj.type = 'U' AND idx.index_id IN (0, 1)

GROUP BY obj.name, prt.rows

ORDER BY SpaceUsedcGB DESC

-- 使用DBCC SHOWCONTIG查看碎片

DBCC SHOWCONTIG('YourDatabaseName.YourTableName');)

##查找索引，先弄聚合索引

exec sp_helpindex 表名

ALTER INDEX @IndexName ON @TableName REBUILD with (online=on);

ALTER INDEX @IndexName ON @TableName REORGANIZE WITH ( LOB_COMPACTION = ON )

-- 重建表上的所有索引

ALTER TABLE table_name REBUILD ALL INDEXES;

查看逻辑文件使用情况语句如下：

SELECT a.name as 逻辑文件名, size/128 [totalspace文件大小(M)],

FILEPROPERTY(a.name, 'SpaceUsed')/128 [usedspace已用空间(M)],

size/128 - FILEPROPERTY(a.name, 'SpaceUsed')/128 [未用空间(M)],

round(FILEPROPERTY(a.name, 'SpaceUsed')*100.0/size,0) [使用率(%)]

FROM sys.database_files a cross join (select recovery_model_desc, log_reuse_wait,log_reuse_wait_desc,is_auto_shrink_on from sys.databases where name=DB_NAME())b

WHERE type=0

order by 5 desc;

若未用空间足够大，则对逻辑文件空间进行释放，执行的语句(例)如下：

--每次收缩 10G

declare @usedspace int ,@totalspace int

select @usedspace = 1653224 ,@totalspace = 1809720

while @totalspace > @usedspace

begin

set @totalspace = @totalspace - 10 *1024(可修改，但不要释放过大)

DBCC SHRINKFILE (N'MES_ODS' , @totalspace)

end

查看释放空间的进度：

select blocking_session_id,percent_complete from sys.dm_exec_requests where command like '%Dbcc%'