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)

No comments:

Post a Comment

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...