Tuesday 12 May 2015

Simplify and Shrink

The following scripts sets the non system databases to simple recovery model, and shrinks the related log files:

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''Recovery'')=''FULL''
    and   DatabasePropertyEx(''?'', ''Status'')=''ONLINE''
    and ''?'' not in (''tempdb'') and ''?'' not in (''master'') and ''?'' not in (''model'') and ''?'' not in (''msdb'')
begin
  exec ('' print char(13) + char(10) + ''''Set recovery model to simple for '''' + ''''?'''';
 alter database [?] set recovery simple with NO_WAIT;'')
end'

EXEC sp_MSforeachdb 
N'IF DatabasePropertyEx(''?'', ''Status'')=''ONLINE''
    and ''?'' not in (''tempdb'') and ''?'' not in (''master'') and ''?'' not in (''model'') and ''?'' not in (''msdb'')
begin
 exec ('' use [?];
 declare @logFile varchar(128);
 select @logFile= mf.name from sys.master_files mf inner join sys.databases db on mf.database_id = db.database_id where type=1 and db.name = ''''?'''';
 print char(13) + char(10) + ''''Shrink ''''+ @logFile + '''' log file of ?'''';
 dbcc shrinkfile (@logFile , 0)
 '')
end'

Caveat: generally, don't use the above or similar scripts in Production, as long as you don't understand and accept the consequences. The Simple recovery model is usually fine in Test or Development environments, but again, it may not be appropriate in many scenarios.

Caveat: always read the message log.

Caveat: the second script assumption is that there is no more than 1 log file per database. If there are multiple log files per database, it would be more sensible to look at a solution which doesn't use sp_MSforeachdb.

Caveat: sp_MSforeachdb is undocumented, and AFAIK unsupported.

Caveat: the code above is provided "as is", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. in no event shall the author be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the code above.