一键帮你收缩所有DB文件大小（Shrink Files for All Databases in SQL Server）

205次阅读

共计 5915 个字符，预计需要花费 15 分钟才能阅读完成。

本文介绍一个简单的 SQL 脚本，实现收缩整个 Microsoft SQL Server 实例所有非系统 DB 文件大小的功能。

作为一个与 SQL 天天打交道的程序猿，经常会遇到 DB 文件太大，把空间占满的情况：

一键帮你收缩所有 DB 文件大小（Shrink Files for All Databases in SQL Server）

而对于开发测试人员来说，如果 DB 数据不是特别重要的话，不会特意扩大磁盘空间，而是直接利用 SQL 的 Shrink File 功能缩小 DB 文件大小，详见：https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file。

这里介绍一个脚本，支持一键收缩整个 SQL Server 实例上所有非系统 DB 文件大小。

脚本支持功能和相关逻辑如下：

五个系统 DB（master、model、msdb、tempdb、Resource）不会执行 Shrink File 操作；
如果 DB 的 Recovery Model 是 FULL 或者 BULK_LOGGED，会自动改成 SIMPLE，Shrink File 操作后再改回原来的；
只会对 DB 状态为 ONLINE 的 DB 进行 Shrink File 操作；
DB 的所有文件，包括数据文件和日志文件都会被执行收缩操作（Shrink Database Data Files & Shrink Database Log Files）；
执行 SQL 脚本的用户需要有 sysadmin 或者相关数据库的 DBO 权限。

脚本如下：

— Created by Bob from http://www.cnblogs.com/lavender000/
use master
DECLARE dbCursor CURSOR for select name from [master].[sys].[databases] where state = 0 and is_in_standby = 0;
DECLARE @dbname NVARCHAR(255)
DECLARE @recoveryModel NVARCHAR(255)
DECLARE @tempTSQL NVARCHAR(255)
DECLARE @dbFilesCursor CURSOR
DECLARE @dbFile NVARCHAR(255)
DECLARE @flag BIT

OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
if((@dbname <> ‘master’) and (@dbname <> ‘model’) and (@dbname <> ‘msdb’) and (@dbname <> ‘tempdb’) and (@dbname <> ‘Resource’))
begin
print(”)
print(‘Database [‘ + @dbname + ‘] will be shrinked log…’)
SET @flag = 1
SET @recoveryModel = (SELECT recovery_model_desc FROM sys.databases WHERE name = @dbname)
if((@recoveryModel = ‘FULL’) or (@recoveryModel = ‘BULK_LOGGED’))
begin
SET @tempTSQL = (select CONCAT(‘ALTER DATABASE [‘, @dbname, ‘] SET RECOVERY SIMPLE with no_wait’))
EXEC sp_executesql @tempTSQL
if (@@ERROR = 0)
begin
print(‘ Database [‘ + @dbname + ‘] recovery model has been changed to ”SIMPLE”.’)
SET @flag = 1
end
else
begin
print(‘Database [‘ + @dbname + ‘] recovery model failed to be changed to ”SIMPLE”.’)
SET @flag = 0
end
end

if(@flag = 1)
begin
SET @tempTSQL = (select CONCAT(‘use [‘, @dbname, ‘]’))
EXEC sp_executesql @tempTSQL
SET @dbFilesCursor = CURSOR for select sys.master_files.name from sys.master_files, [master].[sys].[databases] where databases.name = @dbname and databases.database_id = sys.master_files.database_id
open @dbFilesCursor
FETCH NEXT FROM @dbFilesCursor INTO @dbFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tempTSQL = (select CONCAT(‘use [‘, @dbname, ‘] DBCC SHRINKFILE (N”’, @dbFile, ”’) with NO_INFOMSGS’))
EXEC sp_executesql @tempTSQL
if(@@ERROR = 0) print(‘ Database file [‘ + @dbFile + ‘] has been shrinked log successfully.’)
FETCH NEXT FROM @dbFilesCursor INTO @dbFile
END
CLOSE @dbFilesCursor
DEALLOCATE @dbFilesCursor

if(@recoveryModel <> ‘SIMPLE’)
begin
— Finally changed back
SET @tempTSQL = (select CONCAT(‘ALTER DATABASE [‘, @dbname, ‘] SET RECOVERY ‘, @recoveryModel, ‘ with no_wait’))
EXEC sp_executesql @tempTSQL
if (@@ERROR = 0)
begin
print(‘ Database [‘ + @dbname + ‘] recovery model has been changed back to ”’ + @recoveryModel + ””)
end
else
begin
print(‘ Database [‘ + @dbname + ‘] recovery model failed to be changed back to ”’ + @recoveryModel + ””)
end
end
end
end
FETCH NEXT FROM dbCursor INTO @dbname
END

CLOSE dbCursor
DEALLOCATE dbCursor

执行完效果如下：

一键帮你收缩所有 DB 文件大小（Shrink Files for All Databases in SQL Server）

Note：

如果不放心使用，可提前备份相关数据库；
使用前请仔细阅读脚本支持功能和相关逻辑，如与自己需求不符，请不要使用该脚本，或者请根据自己需求自行修改脚本；
脚本为简易脚本，仅用于测试学习，可能有 BUG，不可生产环境使用，如有错误，请留言。

更新：该脚本在 SQL Server 2008R2 上执行可能会报如下错误：

‘CONCAT’ is not a recognized built-in function name.

由于脚本中使用了 CONCAT 函数，该函数是 TSQL 在 SQL Server 2012 中新引入的函数，具体详见：https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql。

因此如果想在 SQL Server 2012 以下版本中使用的话需要把 CONCAT 函数改成 string + 方法，具体如下：

— Created by Bob from http://www.cnblogs.com/lavender000/
use master
DECLARE dbCursor CURSOR for select name from [master].[sys].[databases] where state = 0 and is_in_standby = 0;
DECLARE @dbname NVARCHAR(255)
DECLARE @recoveryModel NVARCHAR(255)
DECLARE @tempTSQL NVARCHAR(255)
DECLARE @dbFilesCursor CURSOR
DECLARE @dbFile NVARCHAR(255)
DECLARE @flag BIT

OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
if((@dbname <> ‘master’) and (@dbname <> ‘model’) and (@dbname <> ‘msdb’) and (@dbname <> ‘tempdb’) and (@dbname <> ‘Resource’))
begin
print(”)
print(‘Database [‘ + @dbname + ‘] will be shrinked log…’)
SET @flag = 1
SET @recoveryModel = (SELECT recovery_model_desc FROM sys.databases WHERE name = @dbname)
if((@recoveryModel = ‘FULL’) or (@recoveryModel = ‘BULK_LOGGED’))
begin
SET @tempTSQL = (‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY SIMPLE with no_wait’)
EXEC sp_executesql @tempTSQL
if (@@ERROR = 0)
begin
print(‘ Database [‘ + @dbname + ‘] recovery model has been changed to ”SIMPLE”.’)
SET @flag = 1
end
else
begin
print(‘Database [‘ + @dbname + ‘] recovery model failed to be changed to ”SIMPLE”.’)
SET @flag = 0
end
end

if(@flag = 1)
begin
SET @tempTSQL = (‘use [‘ + @dbname + ‘]’)
EXEC sp_executesql @tempTSQL
SET @dbFilesCursor = CURSOR for select sys.master_files.name from sys.master_files, [master].[sys].[databases] where databases.name = @dbname and databases.database_id = sys.master_files.database_id
open @dbFilesCursor
FETCH NEXT FROM @dbFilesCursor INTO @dbFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tempTSQL = (‘use [‘ + @dbname + ‘] DBCC SHRINKFILE (N”’ + @dbFile + ”’) with NO_INFOMSGS’)
EXEC sp_executesql @tempTSQL
if(@@ERROR = 0) print(‘ Database file [‘ + @dbFile + ‘] has been shrinked log successfully.’)
FETCH NEXT FROM @dbFilesCursor INTO @dbFile
END
CLOSE @dbFilesCursor
DEALLOCATE @dbFilesCursor

if(@recoveryModel <> ‘SIMPLE’)
begin
— Finally changed back
SET @tempTSQL = (‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY ‘ + @recoveryModel + ‘ with no_wait’)
EXEC sp_executesql @tempTSQL
if (@@ERROR = 0)
begin
print(‘ Database [‘ + @dbname + ‘] recovery model has been changed back to ”’ + @recoveryModel + ””)
end
else
begin
print(‘ Database [‘ + @dbname + ‘] recovery model failed to be changed back to ”’ + @recoveryModel + ””)
end
end
end
end
FETCH NEXT FROM dbCursor INTO @dbname
END