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'') begin set @logname = (Select name From Sys.master_files where DB_ID(''?'') = database_id and type=1) print ''Use [?];'' print ''DBCC SHRINKFILE (['' + @logname + ''] ,100)'' end '
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'') begin set @logname = (Select name From [?].Sys.database_files where type=1) EXEC(''Use [?]; DBCC SHRINKFILE (['' + @logname + ''] ,100)'') end '
You may be interested to know more on sp_MSforeachdb usage, please visit the below links