Script to shrink t-sql logs for all databases on a server

Often on servers we have way too many databases to shrink logs individually when the server seems to be running low on space.
Here is a script which will in turn generate a script to do this for you.

SELECT name INTO #tempDBs FROM master..sysdatabases
where name not in ('master','tempdb','model','msdb')

DECLARE @CurrentDBName varchar(100)
DECLARE @CurrentDBLog varchar(100)
DECLARE @query varchar(1000)

DECLARE @AllDBsCursor CURSOR
SET @AllDBsCursor = CURSOR FAST_FORWARD
FOR

SELECT DB_NAME(database_id), name FROM sys.master_files WHERE DB_NAME(database_id) in
(select name from #tempDBs) and physical_name like '%ldf'
-- and DB_NAME(database_id) like '%joyce%'

OPEN @AllDBsCursor
FETCH NEXT FROM @AllDBsCursor
INTO @CurrentDBName, @CurrentDBLog
WHILE @@FETCH_STATUS = 0
BEGIN

set @query = 'USE ['+@CurrentDBName+']
GO
ALTER DATABASE ['+ @CurrentDBName +']
SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE('''+@CurrentDBLog+''', 1) 
GO
ALTER DATABASE ['+@CurrentDBName+']
SET RECOVERY FULL WITH NO_WAIT
GO'

PRINT @query

FETCH NEXT FROM @AllDBsCursor
INTO @CurrentDBName, @CurrentDBLog
END
CLOSE @AllDBsCursor
DEALLOCATE @AllDBsCursor

DROP TABLE #tempDBs
Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: