Everyone knows how frustrating dealing with SQL backups can be.
This is a script to automatically generate the truncate statements for all databases on a SQL server and order them biggest to smallest.
Just run the script on a SQL server, then copy the output of the [cmd] column in a new query window for the database you'd like to shrink.
Be aware though, this will cause your point in time recoverability to restart at the point this script is run.
Be sure you understand what the ramifications of this are.
SELECT d.NAME,
log_size / 1024 AS log_size_mb,
'USE [master]; ALTER DATABASE [' + d.NAME + '] SET RECOVERY SIMPLE WITH NO_WAIT; USE ' + d.NAME
+ '; DECLARE @FILENAME AS VARCHAR(2000); SELECT @FILENAME=name FROM sys.database_files WHERE type_desc = ''LOG''; DBCC SHRINKFILE(@FILENAME, 1); ALTER DATABASE [' + d.NAME + '] SET RECOVERY FULL WITH NO_WAIT; ' AS cmd
FROM sys.databases AS d
INNER JOIN (SELECT Rtrim(instance_name) [database],
cntr_value log_size
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Databases'
AND counter_name = 'Log File(s) Used Size (KB)'
AND instance_name <> '_Total') s
ON d.NAME = s.[database]
WHERE d.NAME NOT LIKE 'Report%'
ORDER BY log_size DESC
