Monday, March 2, 2015

Script to shrink all database log files in SQL Server

The below script is useful when ever you want to shrink all log files of user databases ( mostly you may need to do in test & dev environments) to free up the disk space. if you ran below script you can get the  DBCC SHRINKFILE script for each database. Change the results to text option before executing below script shown in Pic :1. Copy the results

(Pic : 1)
in new window past the copied results shown in Pic : 2 and execute it to shrink the log file.

SELECT  CHAR(13)+CHAR(10) + 'USE ['+ DB_NAME(database_id)+']'+CHAR(13)+CHAR(10) + 'GO'+CHAR(13)+CHAR(10) + 'DBCC SHRINKFILE (N'''+name+''' , 0, TRUNCATEONLY)'+CHAR(13)+CHAR(10) + 'GO'  FROM sys.master_files where file_id = 2   

(Pic :2)