-- Stored Procedure: dbo.ShrinkLogs
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ShrinkLogs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ShrinkLogs]
GO





CREATE PROCEDURE [dbo].[ShrinkLogs] 
AS
/*******************************************************************************************************
* admin.dbo.ShrinkLogs 
* Creator:      Bill Wunder
* Date:   2-23-2003
*
* Description: shrinks all logs larger than 128MB for DBs with SIMPLE recovery model to 100MB
* Notes:      
*
* Usage:
      EXECUTE admin.dbo.ShrinkLogs
*
*
* Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*         bw                 5-13-03  only attemp to shrink if log file is over 128MB       
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session
---------------------------------------------
SET NOCOUNT ON

---------------------------------------------
-- body of stored procedure
---------------------------------------------
exec sp_msforeachdb 'If DATABASEPROPERTYEX(''?'', ''Recovery'') = ''SIMPLE''
 begin
  use ?
  set nocount on 
  declare @SQLStr nvarchar(255)
  create table #files 
   (name varchar(128), 
   fileid tinyint, 
   filename varchar(128), 
   filegroup varchar(128), 
   size varchar(20), 
   maxsize varchar(20), 
   growth varchar(20), 
   usage varchar(10))
  insert #files exec sp_helpfile
  select @SQLStr = ''DBCC SHRINKFILE('' + cast(min(fileid) as varchar(5)) + '',100)''
  from #files where usage = ''log only''
  and case when charindex(''KB'',size) > 0 
    then cast(substring(size, 1, charindex(''KB'',size) - 2) as int) 
          when charindex(''MB'',size) > 0
         then cast(substring(size, 1, charindex(''MB'',size) - 2) as int) * 1000 
   else 0
   end > 128000
  print db_name()
  if @sqlstr is not null
   exec sp_executesql @SQLStr
 end'



GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO