XML FeedsA recent post in the forum I frequent presented an interesting problem - shrink log file in all user databases. See the reasons why it may not be a good idea explained in Do not truncate your LDF files and Why you want to be restrictive with shrink of database files.
The first idea that came to mind was to use sp_MSForEachDB non-documented stored procedure for this task. You can find this article by Arshad Ali very helpful in understanding sp_MsForEachTable and sp_MSForEachDB stored procedures and their parameters.
Follow up:
UPDATE
Based on Duncan's comments the better code than I suggested would be
- sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
- AND (SELECT recovery_model FROM master.sys.databases WHERE name = ''?'') = 1
- AND (SELECT is_read_only FROM master.sys.databases WHERE name = ''?'') = 0
- BEGIN
- declare @LogFile nvarchar(2000)
- USE [?]
- SELECT @LogFile = sys.database_files.name
- FROM sys.database_files
- WHERE (sys.database_files.type = 1)
- PRINT @LogFile
- EXEC(''ALTER DATABASE [?] SET RECOVERY SIMPLE'')
- DBCC SHRINKFILE (@LogFile, 1)
- EXEC(''ALTER DATABASE [?] SET RECOVERY FULL'')
- END'
It may be even a better idea of using dynamic SQL and implicit looping through databases instead of undocumented SP, like this
- declare @SQL nvarchar(max)
- select @SQL = coalesce(@SQL + char(13) + char(10),'') + N'
- Use ' + QUOTENAME(d.[name]) + ';' + CHAR(13) + '
- ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY SIMPLE;
- DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1);
- ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY FULL;'
- FROM sys.databases d
- INNER JOIN sys.master_files mf ON [d].[database_id] = [mf].[database_id]
- WHERE
- d.[database_id] > 4 --no sys dbs
- AND d.recovery_model = 1
- AND d.is_read_only = 0
- AND mf.[type] = 1 --log files
- ORDER BY d.name
- --print @SQL
- execute (@SQL)
(code based on this MSDN thread)
-------------------------
Interestingly, this solution, proposed by Borislav Borissov, still attempts to change recovery model in TempDB - not clear why:
- sp_MSForEachDb 'IF LOWER(''?'') NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
- BEGIN
- declare @LogFile nvarchar(2000)
- declare @ExeString nvarchar(2000)
- USE [?]
- SELECT @LogFile = sys.sysaltfiles.name
- FROM sys.sysdatabases
- INNER JOIN sys.sysaltfiles ON sys.sysdatabases.dbid = sys.sysaltfiles.dbid
- WHERE (sys.sysaltfiles.fileid = 1) AND (sys.sysdatabases.name = ''?'')
- print @LogFile
- ALTER DATABASE [?] SET RECOVERY SIMPLE
- DBCC SHRINKFILE (@LogFile, 1)
- ALTER DATABASE [?] SET RECOVERY FULL
- END'
However, the solution suggested by George Mastros of first creating a stored procedure and then executing it, works fine
- sp_msforeachdb 'If ''?'' Not In (''master'',''tempdb'',''model'',''msdb'')
- Begin
- Declare @LogFile nvarchar(2000)
- Select @LogFile = Name From master..sysaltfiles Where db_name(dbid) = ''?'' And Fileid = 2
- Set @LogFile = ''Create Procedure dbo.ShrinkMe As
- ALTER DATABASE [?] SET RECOVERY SIMPLE
- dbcc ShrinkFile(['' + @LogFile + ''])
- ALTER DATABASE [?] SET RECOVERY FULL''
- use [?]
- If Exists(Select * From [?].Information_Schema.Routines Where Specific_Name = ''ShrinkMe'')
- Begin
- drop Procedure ShrinkMe
- End
- use [?]
- Exec (@LogFile)
- --print(@LogFile)
- exec [?].dbo.ShrinkMe
- End '
This link allows to check the recovery model for the database
http://blog.sqlauthority.com/2009/07/16/sql-server-four-different-ways-to-find-recovery-model-for-database/
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Can't wait for onpnt's reply :-)
I believe onpnt said that Shrinkfile is OK. :)
shrinkfile still causes fragmentation on disk and countless shrinkfiles can become an issue. It is of course the best course if you have a file growth issue and you need to do something about it. I thought of throwing a party when I saw truncate removed in 2008. ;-) Just last week I had to do a shrinkfile on a log that went out there due to a index maintenance and backup issue that made the log go to around 70GB. It's not really a problem but did cause a problem with my mirror and it was a mistake on my part and should have been avoided. 
Good post congratulations. 
I needed a script like this and came across this one. Had to modify it a bit since the first script listed is shrinking file_id 1 (which is the primary data file). I changed this so it is looking at the type field (=1) in master.sys.databases. Also, this script doesn't check to see if the database was in simple recovery model to start with! Changed all of my simple databases to Full. Lastly, the 'tempdb error' is caused by sql validating the generated code for each iteration. Since you can't change the recovery model on tempdb it is spitting out the message. I hid those lines in exec statements.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.