一键帮你收缩所有DB文件大小（Shrink Files for All Databases in SQL Server）
栏目: 数据库 · SQL Server · 发布时间: 7年前
内容简介：一键帮你收缩所有DB文件大小（Shrink Files for All Databases in SQL Server）
本文介绍一个简单的 SQL 脚本，实现收缩整个Microsoft SQL Server实例所有非系统DB文件大小的功能。
作为一个与SQL天天打交道的程序猿，经常会遇到DB文件太大，把空间占满的情况：
而对于开发测试人员来说，如果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
执行完效果如下：
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
CLOSE dbCursor
DEALLOCATE dbCursor
给大家带来的不变深表歉意。
本文永久更新链接地址 ： http://www.linuxidc.com/Linux/2017-06/144839.htm
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
- 用收缩损失 (Shrinkage Loss) 进行深度回归跟踪
- 为什么你不要收缩数据库文件(国外翻译)
- 为你介绍7种流行的线性回归收缩与选择方法（附代码）
- Elasticsearch 入门到高手的成长阶梯-索引的基本操作(2)-索引的收缩和拆分
- ReactNative字体大小不随系统字体大小变化而变化
- JVM 参数最佳实践：元空间的初始大小和最大大小
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
An Introduction to Probability Theory and Its Applications
William Feller / Wiley / 1991-1-1 / USD 120.00
Major changes in this edition include the substitution of probabilistic arguments for combinatorial artifices, and the addition of new sections on branching processes, Markov chains, and the De Moivre......一起来看看 《An Introduction to Probability Theory and Its Applications》 这本书的介绍吧!
