Forcibly Shrink the Transaction Log in All DBs

use master
go
if object_id( 'SP_FORCE_SHRINK_ALL_LOGS' ) is not null drop proc SP_FORCE_SHRINK_ALL_LOGS
go
CREATE PROCEDURE [dbo].[SP_FORCE_SHRINK_ALL_LOGS] AS
--auto runs sp_force_shrink_log on all db log files.
--uses while to loop through all dbs found in information_schema.schemata

CREATE TABLE #db (
[dbID] [int] IDENTITY (1, 1) NOT NULL ,
[db] [varchar] (50) NOT NULL
)

insert into #db (db)
select catalog_name
from information_schema.schemata
order by catalog_name

--Note - can get error if string is too long -> @s maximum is 8000 characters.
declare @s varchar(8000)
set @s = '' --so @s is not null
declare @ID int
declare @Max int
select @ID = min(dbID) from #db
--Nice alternative to cursors!
while @ID is not NULL
BEGIN
select @s = db + '.dbo.sp_force_shrink_log' from #db where dbID = @id
execute (@s) --execute @db.dbo.sp_force_shrink_log
select @ID = min(dbID) from #db where dbID > @ID

END
-- Done!
GO