Script – Shrink log files of all databases
Although we strongly discourage shrinking files of SQL databases, but it can be an absolute necessity at times.
Below is a simple script you can use to shrink log files of all databases and quickly reclaim some space on the drive hosting log files (*.LDF).
DECLARE @dbname VARCHAR(256) DECLARE @logname VARCHAR(256) DECLARE @dbcursor CURSOR SET @dbcursor = CURSOR FOR SELECT DB_Name(database_id), [name] FROM sys.master_files WHERE [type] = 1 OPEN @dbcursor FETCH NEXT FROM @dbcursor INTO @dbname, @logname WHILE @@FETCH_STATUS = 0 BEGIN --PRINT ('USE [' + @dbname + '] DBCC SHRINKFILE (''' + @logname + ''' , 0, TRUNCATEONLY)') --Uncomment this to print the shrink commands EXEC ('USE [' + @dbname + '] DBCC SHRINKFILE (''' + @logname + ''' , 0, TRUNCATEONLY)') FETCH NEXT FROM @dbcursor INTO @dbname, @logname END CLOSE @dbcursor DEALLOCATE @dbcursor
Thank you!
This is extremely helpful, Thanks!
Good post. I am facing many of these issues as well..|
Wow, I like your blog!
Nice!