Ah, I can help with that. The script below will generate a script (scriptception!). So make sure to press ctrl-t before executing the script, so you can get the results in text format. Then, you can copy and paste the result text into a new window and run the script. It will shrink and then truncate all the log files, freeing up space.
Please note that shrinking log files is only useful if you are in a pinch or have run an extraordinary script. Usually, your log files will be around the correct size and shrinking them is only a temporary solution. Expanding the size of the log file and/or buying more disk space is a more lasting solution.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
--8<---------------------------------------------------------------------------- /* PRESS CTRL-T BEFORE RUNNING TO GET THE RESULTS AS TEXT. Copy the resulting text and run it in a new window Author: Kristof Elst Description: Shrinks all log files - first moves data to beginning of file Then truncates file - releases all free space at the end of the file Version: 1.0.0 Date: 2020-09-21 */ --8<---------------------------------------------------------------------------- SET NOCOUNT ON SELECT 'USE [' + DB_NAME(database_id) + N']' + CHAR(13) + CHAR(10) + 'DBCC SHRINKFILE (N''' + msf.name + N''' , 0, NOTRUNCATE)' + CHAR(13) + CHAR(10) + 'GO' FROM sys.master_files msf WHERE msf.database_id > 4 and msf.type = 1 SELECT 'USE [' + DB_NAME(database_id) + N']' + CHAR(13) + CHAR(10) + 'DBCC SHRINKFILE (N''' + msf.name + N''' , 0, TRUNCATEONLY)' + CHAR(13) + CHAR(10) + 'GO' FROM sys.master_files msf WHERE msf.database_id > 4 and msf.type = 1 |