SQL Server 存储管理

1. 查看所有表的数据量及占用空间大小

1.1. 通过 sp_spaceused 存储过程查询

USE $DBName$;
GO

IF OBJECT_ID('tempdb.dbo.#spaceUsed') IS NOT NULL
  DROP TABLE #spaceUsed;

CREATE TABLE #spaceUsed (
  name       NVARCHAR(128)
, rows       CHAR(20)
, reserved   VARCHAR(18)
, data       VARCHAR(18)
, index_size VARCHAR(18)
, unused     VARCHAR(18)
, correctted TINYINT
);

EXEC sys.sp_MSforeachtable '
INSERT #spaceUsed (name, rows, reserved, data, index_size, unused) EXEC sp_spaceused "?"
UPDATE #spaceUsed SET name = "?", correctted = 1 WHERE correctted IS NULL';

SELECT   name
       , FORMAT(CONVERT(INT, rows), '#,0') rows
       , FORMAT(CONVERT(INT, REPLACE(reserved, 'KB', '')), '#,0.##') [reserved (KB)]
       , FORMAT(CONVERT(INT, REPLACE(data, 'KB', '')), '#,0.##') [data (KB)]
       , FORMAT(CONVERT(INT, REPLACE(data, 'KB', '')) / 1024.0, '#,0.##') [data (MB)]
       , FORMAT(CONVERT(INT, REPLACE(data, 'KB', '')) / 1048576.0, '#,0.##') [data (GB)]
       , FORMAT(CONVERT(INT, REPLACE(index_size, 'KB', '')), '#,0.##') [index_size (KB)]
       , FORMAT(CONVERT(INT, REPLACE(unused, 'KB', '')), '#,0.##') [unused (KB)]
FROM     #spaceUsed
ORDER BY CONVERT(INT, REPLACE(data, 'KB', '')) DESC;

1.2. 通过 sys.allocation_units 表查询

USE $DBName$;
GO

SELECT     sch.name schName
         , tbl.name tblName
         , idx.name idxName
         , part.rows rowCounts
         , CAST(ROUND(((SUM(alloc.total_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) totalSpaceGB
         , CAST(ROUND(((SUM(alloc.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) totalSpaceMB
         , CAST(ROUND(((SUM(alloc.used_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) usedSpaceGB
         , CAST(ROUND(((SUM(alloc.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) usedSpaceMB
         , CAST(ROUND(((SUM(alloc.total_pages) - SUM(alloc.used_pages)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) unusedSpaceGB
         , CAST(ROUND(((SUM(alloc.total_pages) - SUM(alloc.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) unusedSpaceMB
FROM       sys.schemas sch
INNER JOIN sys.tables tbl ON tbl.schema_id = sch.schema_id
INNER JOIN sys.indexes idx ON idx.object_id = tbl.object_id
INNER JOIN sys.partitions part ON part.object_id = idx.object_id AND part.index_id = idx.index_id
INNER JOIN sys.allocation_units alloc ON alloc.container_id = part.partition_id
WHERE      tbl.is_ms_shipped = 0 AND idx.object_id > 0
GROUP BY   tbl.name, sch.name, idx.name, part.rows
ORDER BY   totalSpaceMB desc;

2. 大表数据截断

2.1. TRUNCATE 搭配临时表法

USE $DBName$;

BEGIN TRY
  IF @@TRANCOUNT > 0
  BEGIN
    PRINT '>> err: current @@TRANCOUNT > 0';
    RETURN;
  END;

  BEGIN TRAN;

  DECLARE @newSeed INT; -- 继续在原有的 ID 上递增
  SELECT @newSeed = MAX(ID) + 1 FROM $dbo$.$TblName$ WITH (TABLOCKX); -- 启用表锁，避免插入新数据

  SELECT * INTO #reserve FROM $dbo$.$TblName$ WHERE $YourConditions$;

  TRUNCATE TABLE $dbo$.$TblName$;

  DBCC CHECKIDENT('$dbo$.$TblName$', RESEED, @newSeed);

  SET IDENTITY_INSERT $dbo$.$TblName$ ON;
  INSERT INTO $dbo$.$TblName$ ($cols$) SELECT $cols$ FROM #reserve;
  SET IDENTITY_INSERT $dbo$.$TblName$ OFF;

  DROP TABLE #reserve;

  COMMIT TRAN;
END TRY
BEGIN CATCH
  ROLLBACK TRAN;
  PRINT '>> err: ' + ERROR_MESSAGE() + N' (' + CAST(ERROR_LINE() AS NVARCHAR(20)) + N')';
END CATCH;

2.2. 间歇性 DELETE 法

USE $DBName$;

WHILE EXISTS (SELECT 1 FROM $dbo$.$TblName$ WHERE $YourConditions$)
BEGIN
  DELETE TOP (1000) FROM $dbo$.$TblName$ WHERE $YourConditions$; -- 每次删除 1000 行数据

  WAITFOR DELAY '00:00:05'; -- 等待 5 秒钟，再继续删除
END;

3. 收缩数据库文件

Note

1. 如果收缩的文件比较大，建议分多次进行收缩，每次收缩一些，否则收缩时间会长到难以置信，并且最终还收缩失败；
2. 建议在此命令之后追加执行清理索引碎片，数据库文件收缩后会产生较多的索引碎片；

USE $DBName$;
GO

DECLARE @TARGET_FREE_PERCENT FLOAT = 0.1, @MANUAL_TARGET_SIZE_MB INT = 0;
DECLARE @sizeMB INT, @usedMB INT, @tarSizeMB INT;

SELECT @sizeMB = dbfile.size / 128
     , @usedMB = FILEPROPERTY(dbfile.name, 'SpaceUsed') / 128
     , @tarSizeMB = CEILING(FILEPROPERTY(dbfile.name, 'SpaceUsed') / 128.0 / (1 - @TARGET_FREE_PERCENT))
FROM   sys.database_files dbfile
WHERE  dbfile.type = 0 AND dbfile.physical_name LIKE '%.mdf';

IF @@ROWCOUNT <> 1
BEGIN
  PRINT '>> error: get database file failed';
  RETURN;
END;

IF @MANUAL_TARGET_SIZE_MB > 0
  SET @tarSizeMB = @MANUAL_TARGET_SIZE_MB;

IF @tarSizeMB >= @sizeMB
BEGIN
  PRINT '>> error: new size is bigger than original size';
  RETURN;
END;

SELECT FORMAT(@sizeMB, '#,0.##') currentSizeMB
     , FORMAT(@usedMB, '#,0.##') usedMB
     , FORMAT(CAST(@sizeMB - @usedMB AS FLOAT) / @sizeMB, '#,0.## %') freePercent
     , FORMAT(@tarSizeMB, '#,0.##') tarSizeMB
     , FORMAT(CAST(@tarSizeMB - @usedMB AS FLOAT) / @tarSizeMB, '#,0.## %') tarFreePercent;

DBCC SHRINKFILE('$DBName$', @tarSizeMB);
GO