db Too big? One button to help you shrink all db file sizes (Shrink files for all Databases in SQL Server)

This article describes a simple SQL script that implements the ability to shrink all non-system db file sizes across Microsoft SQL Server instances.

As a program that deals with SQL every day, apes often encounter db files that are too large to fill the space:

For the development tester, if the DB data is not particularly important, do not deliberately expand disk space, but directly using the SQL Shrink file function to reduce the size of the DB files, see: https://docs.microsoft.com/en-us/sql/ Relational-databases/databases/shrink-a-file.

Here is a script that supports shrinking all non-system db file sizes on an entire SQL Server instance with one click.

Script support features and related logic are as follows:

• Five system db (master, model, msdb, tempdb, Resource) do not perform shrink file operations;
• If the recovery model of the DB is full or bulk_logged, it is automatically changed to Simple,shrink file operation and then changed back to original;
• Only DB with DB status of online is shrink file operation;
• All DB files, including data files and log files, are shrunk (Shrink database data files & Shrink database log file);
• The user executing the SQL script needs to have the dbo permissions of the sysadmin or the associated database.

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 @dbFile Scursor CURSOR 8 DECLARE @dbFile NVARCHAR (255) 9 DECLARE @flag BIT10 one OPEN dbCursor12 FETCH NEXT from dbcursor into @dbn  Ame13 while @ @FETCH_STATUS = 015 BEGIN16 if ((@dbname <> ' master ') and (@dbname <> ' model ') and (@dbname         <> ' msdb ') and (@dbname <> ' tempdb ') and (@dbname <> ' Resource ')) begin18 print (") 19 Print (' Database [' + @dbname + '] would be shrinked log ... ') Set @flag = 121 Set @recoveryModel = (S Elect Recovery_model_desc from sys.databases WHERE name = @dbname) The IF ((@recoveryModel = ' full ') or (@recoveryMod el = ' bulk_logged ')) begin24 SET @tempTSQL = (select CONCAT (' ALTER DATABASE [', @dbname, '] SET RECOVERY simple with no_wait ') "EXEC sp_executesql @tempTSQL26 if (@ @ERROR = 0) begin28 print (' Database [' + @dbname + '] Recovery model has been Changed to "simple".) SET @flag = end31 else32 begin33 print (' Databas e [' + @dbname + '] Recovery model failed to being changed to ' simple '. ')             SET @flag = 035 end36 end37 (@flag = 1) begin40             SET @tempTSQL = (select CONCAT (' Use [', @dbname, '] ')) sp_executesql EXEC @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_id43 Open @db FilesCursor44 FETCH NEXT from @dbFilesCursor into @dbFile45             While @ @FETCH_STATUS = 046 BEGIN47 SET @tempTSQL = (The Select CONCAT (' Use [', @dbnam                 E, '] DBCC shrinkfile (N ' ', @dbFile, ') with No_infomsgs '))---EXEC sp_executesql @tempTSQL49 if (@ @ERROR = 0) print (' Database file [' + @dbFile + '] has been shrinked log successfully. ')             FETCH NEXT from @dbFilesCursor to @dbFile51 END52 CLOSE @dbFilesCursor53                 Deallocate @dbFilesCursor54 begin57 (@recoveryModel <> ' simple ') --Finally changed back58 set @tempTSQL = (select CONCAT (' ALTER DATABASE [', @dbname, '] set RECOVERY                 ', @recoveryModel, ' with No_wait ')) sp_executesql EXEC @tempTSQL60 if (@ @ERROR = 0) 61 begin62 print (' Database [' + @dbname + '] Recovery model had been changed back to '        "+ @recoveryModel +") 63         End64 Else begin66 print (' Database [' + @dbname + '] r         Ecovery model failed to being changed back to ' + @recoveryModel + ') end68 end69 END70 end71 FETCH NEXT from dbcursor into @dbname72 END73. CLOSE dbCursor75 deallocate dbcursor

The following results are performed:

Note:

• If you do not trust the use, you can back up the relevant database;
• Please read the script support function and related logic carefully before use, please do not use the script if you do not need it, or you should modify the script according to your own needs.
• scripts are simple scripts, only for test learning, and may have bugs , can not be used in production environment, if there are errors, please leave a message.

Update : The script executed on SQL Server 2008R2 may report the following error:

' CONCAT ' is not a recognized built-in function name.

Because the concat function is used in the script, this function is a newly introduced function of TSQL in SQL Server 2012, as detailed in: https://docs.microsoft.com/en-us/sql/t-sql/functions/ Concat-transact-sql.

Therefore, if you want to use the following version of SQL Server 2012, you need to change the concat function to a string + method, 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 @dbFile Scursor CURSOR 8 DECLARE @dbFile NVARCHAR (255) 9 DECLARE @flag BIT10 one OPEN dbCursor12 FETCH NEXT from dbcursor into @dbn  Ame13 while @ @FETCH_STATUS = 015 BEGIN16 if ((@dbname <> ' master ') and (@dbname <> ' model ') and (@dbname         <> ' msdb ') and (@dbname <> ' tempdb ') and (@dbname <> ' Resource ')) begin18 print (") 19 Print (' Database [' + @dbname + '] would be shrinked log ... ') Set @flag = 121 Set @recoveryModel = (S Elect Recovery_model_desc from sys.databases WHERE name = @dbname) The IF ((@recoveryModel = ' full ') or (@recoveryMod el = ' bulk_logged ')) begin24 SET @tempTSQL = (' ALTER databASE [' + @dbname + '] SET RECOVERY simple with no_wait ') EXEC sp_executesql @tempTSQL26 if (@ @ER ROR = 0) begin28 print (' Database [' + @dbname + '] Recovery model had been changed to ' Simple '. ') SET @flag = end31 else32 begin33 print (' Databas e [' + @dbname + '] Recovery model failed to being changed to ' simple '. ')             SET @flag = 035 end36 end37 (@flag = 1) begin40 Set @tempTSQL = (' use [' + @dbname + '] ') sp_executesql EXEC @tempTSQL set @dbFile Scursor = 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_id43 Open @db FilesCursor44 FETCH NEXT from @dbFilesCursor to @dbFile45 while @ @FETCH_status = 046 BEGIN47 SET @tempTSQL = (' use [' + @dbname + '] DBCC shrinkfile (N ' + @dbFile        + ') no_infomsgs EXEC sp_executesql @tempTSQL49 if (@ @ERROR = 0) print (' Database file [' + @dbFile + '] has been shrinked log successfully. ')             FETCH NEXT from @dbFilesCursor to @dbFile51 END52 CLOSE @dbFilesCursor53                 Deallocate @dbFilesCursor54 begin57 (@recoveryModel <> ' simple ') --Finally changed back58 set @tempTSQL = (' ALTER DATABASE [' + @dbname + '] set RECOVERY ' + @recover                 Ymodel + ' with no_wait ') sp_executesql EXEC @tempTSQL60 if (@ @ERROR = 0) 61 begin62 print (' Database [' + @dbname + '] Recovery model had been changed back to ' + @recov          Erymodel + ") End64 else 65       begin66 print (' Database [' + @dbname + '] Recovery model failed to being changed back to ' + @recoveryModel + ") end68 end69 end70 end71 FETCH NEXT from dbcursor into @d Bname72 END73, CLOSE dbCursor75 deallocate dbcursor

I apologize for the constant change we have brought to you.

[Original articles, reproduced please indicate the source, for study purposes only, if there are errors please leave a message, thank you for your support]

[Original site: Http://www.cnblogs.com/lavender000/p/6882741.html, from the forever-smoked]

db Too big? One button to help you shrink all db file sizes (Shrink files for all Databases in SQL Server)