This script backup and shrink all database.

Everybody says to never use it but shrinking is necessary if your log/data has grown out of control, or as part of a process to remove excessive fragmentation.

Source: http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

Attention : It has parameter, check it.

This SQL Script does:

  • Create backup but it is overwrite.
  • SHRINK DATABASE
  • SHRINK Files
 1
 2PRINT '/*----------------------------------'
 3PRINT 'Shrink all databases without fragmentation'
 4PRINT ''
 5PRINT 'Shrinking is necessary '
 6PRINT 'if your log/data has grown out of control,'
 7PRINT ''
 8PRINT ' * You must check the parameters *'
 9PRINT ''
10PRINT 'Server Name : ' + @@servername
11PRINT 'Date        : ' + CAST(Getdate() as nvarchar(max))
12PRINT ''
13
14DECLARE @ParameterLocalBackupFile AS NVARCHAR(MAX)  = 'D:\SQLServer\backup.DeleteIt.BAK';
15DECLARE @ParameterLocalSizeMaxPercentage AS float = 0.05; -- LogFile Size / Database Size
16--DECLARE @ParameterLocalSizeMaxPercentage AS NVARCHAR(MAX) = -1; -- LogFile Size / Database Size | -1 Force to run on all database
17DECLARE @ParameterMinLogSize as INT = 2048;  -- 2 MByte
18
19PRINT 'Temp backup file name     : ' + @ParameterLocalBackupFile;
20PRINT 'Log/Database size : ' + cast(<a href="mailto:100*@ParameterLocalSizeMaxPercentage">100*@ParameterLocalSizeMaxPercentage</a> as nvarchar(max)) + ' %';
21PRINT ''
22PRINT '----------------------------------*/'
23---------------------------------------
24
25SET NOCOUNT ON;
26IF (OBJECT_ID(N'tempdb..#DatabaseScript') IS NOT NULL) DROP TABLE #DatabaseScript;
27CREATE TABLE #DatabaseScript ( database_id INT, DataBaseName sysname, SqlScript NVARCHAR(MAX) );
28INSERT INTO #DatabaseScript (database_id, DataBaseName, SqlScript)
29SELECT database_id, DB_NAME(database_id),
30CASE WHEN [type] = 0 THEN
31'
32PRINT ''-- Database : '+name+''';
33USE [MASTER];
34BACKUP DATABASE ['+DB_NAME(database_id)+'] TO  DISK = <a href="mailto:N'''+@ParameterLocalBackupFile+'''">N'''+@ParameterLocalBackupFile+'''</a> WITH NOFORMAT, INIT,  NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
35USE ['+DB_NAME(database_id)+'];
36DBCC SHRINKDATABASE(N'''+DB_NAME(database_id)+''');
37DBCC SHRINKFILE (N'''+name+''' , 0, TRUNCATEONLY);
38'
39ELSE
40'
41USE ['+DB_NAME(database_id)+'];
42DBCC SHRINKFILE (N'''+name+''' , 0, TRUNCATEONLY);
43'
44END
45FROM sys.master_files where data_space_id<2 and database_id>4;
46
47-- Filter by percentage
48DELETE FROM #DatabaseScript WHERE database_id NOT in (
49SELECT     tbData.database_id--, tbData.DatabaseName, tbLog.size AS SizeLog, tbData.size AS SizeData, tbLog.size / tbData.size  AS PercentageLog
50FROM
51(SELECT     database_id, DB_NAME(database_id) AS DatabaseName, size FROM sys.master_files WHERE (database_id > 4) AND (type_desc = 'ROWS')) AS tbData INNER JOIN
52(SELECT     database_id, DB_NAME(database_id) AS DatabaseName, size FROM sys.master_files AS master_files_1 WHERE (database_id > 4) AND (type_desc = 'LOG')) AS tbLog
53ON tbData.database_id = tbLog.database_id
54WHERE (0.0+tbLog.size) / tbData.size > @ParameterLocalSizeMaxPercentage
55)
56DECLARE @DataBaseName sysname, @SqlScript NVARCHAR(MAX);
57WHILE (exists (SELECT * FROM #DatabaseScript) )
58BEGIN
59    SELECT TOP 1 @DataBaseName = DataBaseName, @SqlScript=SqlScript FROM #DatabaseScript;
60    DELETE FROM #DatabaseScript WHERE @DataBaseName = DataBaseName AND @SqlScript=SqlScript ;
61    PRINT @SqlScript;
62    --EXEC(@SqlScript);
63END
64PRINT '/*'
65PRINT Getdate()
66PRINT '*/'
67PRINT '----------------------------------'
68----------------------------------------------------------------