Archive for category Sql Server

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

, ,

Leave a comment