This paper introduces a simple SQL script to shrink the size of all non-system DB files in the whole Microsoft SQL Server instance.

As a program ape dealing with SQL every day, it often encounters the situation that DB files are too big and space is full:

For developers and testers, if DB data is not particularly important, they will not intentionally expand disk space, but directly use the Shrink File function of SQL to reduce the size of DB files. For more details, see: https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file.

Here's a script that supports one-click shrinkage of all non-system DB file sizes across the entire SQL Server instance.

The script support functions and related logic are as follows:

• Five systems DB (master, model, msdb, tempdb, Resource) do not perform Shrink File operations.
• If DB's Recovery Model is FULL or BULK_LOGGED, it will automatically change to SIMPLE and Shrink File will change back to the original after operation.
• Only Shrink File operations are performed on DB with ONLINE status.
• All files of DB, including data files and log files, will be contracted.
• Users executing SQL scripts need DBO privileges for sysadmin or related databases.

The script is as follows:

 1 -- 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

The results are as follows:

Note:

• If you are not at ease, you can backup the relevant database in advance.
• Before using, please read the script support function and related logic carefully. If it does not meet your own needs, please do not use the script, or modify the script according to your own needs.
• The script is a simple script, only for testing and learning, there may be BUG, can not be used in production environment, if there are errors, please leave a message.

[Original articles, reprinted please indicate the source, only for learning and research purposes, if there are errors, please leave a message, thank you for your support]

[Original site: http://www.cnblogs.com/lavender000/p/6882741.html From Forever smoked]