Shrink Tlog Files

,

DBA many a times need to shrink the log files if that grows very large. Though I am not recommending to do it daily or often but if space is an issue and you want to release space to OS, you may use the script.

Copy the script onto SQL Server Management Studio

Run it.

Copy the string from cmd column and run it. Do not run all the shrink against all databases together. You need to review properly before you run the script as this is just for the purpose of assisting other DBAs. If any performance issue occurs I may not be able to guide.

-- create a temporary table to insert the above mentioned output against each databases.

-- Check the Tlog files to shrink
SET NOCOUNT on
create table #db_files(
--Id INT IDENTITY(1,1),//use row_number() function.
DB_name VARCHAR(MAX),
Recovery_model VARCHAR(200),
db_files varchar(4000),
file_loc varchar(4000),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2)
--,cmd varchar(4000)
)

declare @strSQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @getDBname CURSOR

SET @getDBname = CURSOR FOR
select name FROM sys.databases
WHERE  state_desc = 'ONLINE'

OPEN @getDBname
FETCH NEXT
FROM @getDBname INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @dbName

select @strSQL = 
    '
        use ' + quotename(@dbname) + '
        INSERT INTO #db_files
        select '''+ @dbname+'''
	,Convert(varchar(100), DATABASEPROPERTYEX(''' + @dbname + ''',''recovery''))
     , name
    , physical_name
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
	--, ''use '' + QUOTENAME(db_name) + '';''+ CHAR(13) + ''DBCC ShrinkFile('' + db_files +'',TRUNCATEONLY);'' + CHAR(13)''''
	 from sys.database_files a
	WHERE type_desc = ''LOG'' -- signifies log file
    '


   --select @strSQL
   exec sp_executesql @strSQL

FETCH NEXT
FROM @getDBname INTO @dbName

END
CLOSE @getDBname
DEALLOCATE @getDBname


ALTER TABLE #db_files ADD cmd VARCHAR(2000) NULL;


UPDATE #db_files
SET cmd = 'USe ' + QUOTENAME(db_name) + ';'+ CHAR(13) + 'DBCC ShrinkFile(''' + db_files +''',TRUNCATEONLY);' + CHAR(13)
FROM #db_files

SELECT  * FROM #db_files
ORDER BY FreespaceMB desc


GO
DROP TABLE #db_files

GO

Rate

3.57 (7)

Share

Share

Rate

3.57 (7)