sp_msforeachdb: Shrinking log files for all databases in SQL Server

This post is mainly to answer a question in one of my earlier post https://latheeshnk.wordpress.com/2015/06/02/sp_msforeachdb-changing-recovery-model-for-all-databases/


Mr Yagnesh was looking for a script to shrink log file for all of his databases using sp_msforeachdb.

To answer the question, Yes, we can very well modify the script in the post to shrink the log files. However, SHRINKFILE file is not advisable in all situations, I would suggest to take that option with extreme case.

You may find the below script to *ONLY* generate the SHRINKFILE DBCC commands for all databases except system databases. The reason for generating the DBCC commands is to verify the commands before you execute in your environment as a first level of verification to avoid any surprises.


 --To avoid/exception on applying the change, you may add your databases in the list. 
EXEC sp_msforeachdb 'Declare @logname varchar(500) 
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
        set @logname = (Select name From Sys.master_files where DB_ID(''?'') = database_id and type=1) 
        print ''Use [?];'' 
        print ''DBCC SHRINKFILE (['' + @logname + ''] ,100)'' 

Alternatively, there is very simple method as below:

Select 'Use [' + db_name(database_id) + '];DBCC SHRINKFILE(['+ name + '],100)' 
	From sys.master_files where type=1
		and db_name(database_id) not in ('tempdb','master','msdb','model','Reportserver','ReportserverTempDB')

EDIT: 19th May 2017

One of my friend had difficulty to change the above to a dynamic execution statement. The below is my try to help those find it difficult:

EXEC sp_msforeachdb ' Declare @logname varchar(500) = '''';

IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
	set @logname = (Select name From [?].Sys.database_files where  type=1) 

	EXEC(''Use [?];
		DBCC SHRINKFILE (['' + @logname + ''] ,100)'')  

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links


One thought on “sp_msforeachdb: Shrinking log files for all databases in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s