当前位置：Gxlcms >
数据库问题 >
DB太大？一键帮你收缩所有DB文件大小（Shrink Files for All Databases in SQL Server）
DB太大？一键帮你收缩所有DB文件大小（Shrink Files for All Databases in SQL Server）
时间：2021-07-01 10:21:17
帮助过：9人阅读
-- Created by Bob from http://www.cnblogs.com/lavender000/
2 use master
3 DECLARE dbCursor
CURSOR for select name
from [master].
[sys].
[databases] where state
= 0 and is_in_standby
= 0;
4 DECLARE @dbname NVARCHAR(
255)
5 DECLARE @recoveryModel NVARCHAR(
255)
6 DECLARE @tempTSQL NVARCHAR(
255)
7 DECLARE @dbFilesCursor CURSOR
8 DECLARE @dbFile NVARCHAR(
255)
9 DECLARE @flag BIT
10
11 OPEN dbCursor
12 FETCH NEXT FROM dbCursor
INTO @dbname
13
14 WHILE @@FETCH_STATUS = 0
15 BEGIN
16 if((
@dbname <> ‘master‘)
and (
@dbname <> ‘model‘)
and (
@dbname <> ‘msdb‘)
and (
@dbname <> ‘tempdb‘)
and (
@dbname <> ‘Resource‘))
17 begin
18 print(
‘‘)
19 print(
‘Database [‘ + @dbname + ‘] will be shrinked log...‘)
20 SET @flag = 1
21 SET @recoveryModel = (
SELECT recovery_model_desc
FROM sys.databases
WHERE name
= @dbname)
22 if((
@recoveryModel = ‘FULL‘)
or (
@recoveryModel = ‘BULK_LOGGED‘))
23 begin
24 SET @tempTSQL = (
select CONCAT(
‘ALTER DATABASE [‘,
@dbname,
‘] SET RECOVERY SIMPLE with no_wait‘))
25 EXEC sp_executesql
@tempTSQL
26 if (
@@ERROR = 0)
27 begin
28 print(
‘ Database [‘ + @dbname + ‘] recovery model has been changed to ‘‘SIMPLE‘‘.‘)
29 SET @flag = 1
30 end
31 else
32 begin
33 print(
‘Database [‘ + @dbname + ‘] recovery model failed to be changed to ‘‘SIMPLE‘‘.‘)
34 SET @flag = 0
35 end
36 end
37
38 if(
@flag = 1)
39 begin
40 SET @tempTSQL = (
select CONCAT(
‘use [‘,
@dbname,
‘]‘))
41 EXEC sp_executesql
@tempTSQL
42 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
43 open @dbFilesCursor
44 FETCH NEXT FROM @dbFilesCursor INTO @dbFile
45 WHILE @@FETCH_STATUS = 0
46 BEGIN
47 SET @tempTSQL = (
select CONCAT(
‘use [‘,
@dbname,
‘] DBCC SHRINKFILE (N‘‘‘,
@dbFile,
‘‘‘) with NO_INFOMSGS‘))
48 EXEC sp_executesql
@tempTSQL
49 if(
@@ERROR = 0)
print(
‘ Database file [‘ + @dbFile + ‘] has been shrinked log successfully.‘)
50 FETCH NEXT FROM @dbFilesCursor INTO @dbFile
51 END
52 CLOSE @dbFilesCursor
53 DEALLOCATE @dbFilesCursor
54
55 if(
@recoveryModel <> ‘SIMPLE‘)
56 begin
57 -- Finally changed back
58 SET @tempTSQL = (
select CONCAT(
‘ALTER DATABASE [‘,
@dbname,
‘] SET RECOVERY ‘,
@recoveryModel,
‘ with no_wait‘))
59 EXEC sp_executesql
@tempTSQL
60 if (
@@ERROR = 0)
61 begin
62 print(
‘ Database [‘ + @dbname + ‘] recovery model has been changed back to ‘‘‘ + @recoveryModel + ‘‘‘‘)
63 end
64 else
65 begin
66 print(
‘ Database [‘ + @dbname + ‘] recovery model failed to be changed back to ‘‘‘ + @recoveryModel + ‘‘‘‘)
67 end
68 end
69 end
70 end
71 FETCH NEXT FROM dbCursor
INTO @dbname
72 END
73
74 CLOSE dbCursor
75 DEALLOCATE dbCursor
执行完效果如下：
Note：
- 如果不放心使用，可提前备份相关数据库；
- 使用前请仔细阅读脚本支持功能和相关逻辑，如与自己需求不符，请不要使用该脚本，或者请根据自己需求自行修改脚本；
- 脚本为简易脚本，仅用于测试学习，可能有BUG，不可生产环境使用，如有错误，请留言。
[原创文章，转载请注明出处，仅供学习研究之用，如有错误请留言，谢谢支持]
[原站点：http://www.cnblogs.com/lavender000/p/6882741.html，来自永远薰薰]
DB太大？一键帮你收缩所有DB文件大小（Shrink Files for All Databases in SQL Server）
标签：测试 ror targe online with server 日志 放心 files
