Published August 13, 2018 by

SQL Server shrinkdatabase of all databases in MSSQL Server

Best Practices

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

Consider the following information when you plan to shrink a file:

  • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
  • Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  • A shrink operation does not preserve the fragmentation state of indexes in the database and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
  • Shrink multiple files in the same database sequentially instead of concurrently. Contention on system tables can cause delays due to blocking.

The following script helps you to shrink Log files and it includes the following steps

  1. Create a Temp Table and hold the entire database names from the database server
  2. insert all the database name and corresponding log files names into the temp table
  3. go through each row and execute the shrinkfile script against each database log file on the server.
  4. clean up

This script will exclude system databases.firstly script will change the Recovery Mode to Simple and shrink log file again set recovery Mode to Full.

CREATE TABLE #TransactionLogFiles (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) )
-- step 1. get hold of the entire database names from the database server
DECLARE DataBaseList CURSOR FOR 
SELECT name FROM SYS.sysdatabases

WHERE NAME NOT IN ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')


DECLARE @DataBase VARCHAR(128)
DECLARE @SqlScript VARCHAR(MAX) 
-- step 2. insert all the database name and corresponding log files' names into the temp table
OPEN DataBaseList FETCH
NEXT FROM DataBaseList INTO @DataBase
WHILE @@FETCH_STATUS <> -1 
BEGIN

SET @SqlScript = 'USE [' + @DataBase + '] INSERT INTO #TransactionLogFiles(DatabaseName, LogFileName) SELECT '''
+ @DataBase + ''', Name FROM sysfiles WHERE FileID=2'
--SELECT @SqlScript
EXEC(@SqlScript) 
FETCH NEXT FROM DataBaseList INTO @DataBase END

DEALLOCATE DataBaseList

-- step 3. go through the each row and execute the shrinkfile script against each database log file on the server
DECLARE TransactionLogList CURSOR FOR 
SELECT DatabaseName, LogFileName FROM #TransactionLogFiles 
DECLARE @LogFile VARCHAR(128) 

OPEN TransactionLogList FETCH
NEXT FROM TransactionLogList INTO @DataBase, @LogFile
WHILE @@FETCH_STATUS <> -1 
BEGIN 
SELECT @SqlScript = 'USE [' + @DataBase + '] '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY SIMPLE WITH NO_WAIT '
+ 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) '
+ 'ALTER DATABASE [' + @DataBase + '] SET RECOVERY FULL WITH NO_WAIT'
EXEC(@SqlScript)  

FETCH NEXT FROM TransactionLogList INTO @DataBase, @LogFile END
DEALLOCATE TransactionLogList
--SELECT * FROM #TransactionLogFiles

-- step 4. clean up
DROP TABLE #TransactionLogFiles

Windows

comments

Comments