531
Managing database files’ size is a critical task for any database administrator. One common scenario is to shrink the log file when it becomes too large. Shrinking the log file can free up disk space and improve performance. This script generates a SQL script that shrinks the log file of all user databases except for system databases, using the TRUNCATEONLY and EMPTYFILE options. It provides an efficient way for DBAs to automate the log file shrinking process and keep the database files at a manageable size.
T-Sql
USE [MASTER]
GO
SELECT '--' + physical_name + CHAR(13) +
'USE ' + '[' + sd.[name] + ']' + CHAR(13) +
'GO' + CHAR(13) +
'DBCC SHRINKFILE (N''' + mf.[name] + ''',' + ' 0,' + ' TRUNCATEONLY' + ')' + CHAR(13) +
'GO' + CHAR(13) + CHAR(13) +
'USE ' + '[' + sd.[name] + ']' + CHAR(13) +
'GO' + CHAR(13) +
'DBCC SHRINKFILE (N''' + mf.[name] + ''',' + ' 1000)' + CHAR(13) +
'GO' + CHAR(13) + CHAR(13) +
'USE ' + '[' + sd.[name] + ']' + CHAR(13) +
'GO' + CHAR(13) +
'DBCC SHRINKFILE (N''' + mf.[name] + ''',' + ' EMPTYFILE' + ')' + CHAR(13) +
'GO' + CHAR(13) + CHAR(13)
FROM
sys.master_files mf
LEFT JOIN
sys.databases sd
ON mf.database_id = sd.database_id
WHERE
mf.[file_id] = 2
AND sd.[name] NOT IN('master', 'tempdb', 'model', 'msdb')
ORDER BY sd.[name]