Developers were recovering test databases with FULL recovery model enabled. Because it was on a test server, no backups were setup and logs would quickly take up a lot of storage space!
I tried 2 options:
Backup logs to null
---- Backup logs to nul exec sp_MSforeachdb ' if "?" not in ("master", "model", "msdb", "tempdb") backup log [?] to disk = "nul";'
Somehow sp_MSforeachdb seems not to apply filters correctly and the job was terminating with an error, moreover it would be required to filter out DBs using simple recovery model.
Also, you still need to run a shrink maintenance task to reclaim space to the OS.
Convert databases to SIMPLE recovery model
With SIMPLE recovery model as opposed to FULL, no log are kept for transaction tracking and recovery. That’s somewhat blunt approach but good enough for test environement.
You can check databases using FULL recovery model with:
SELECT name FROM sys.databases WHERE recovery_model_desc = 'FULL' ; GO
And to convert databases not yet using SIMPLE recovery model:
USE master ; DECLARE @name VARCHAR(100) -- database name DECLARE db_cursor CURSOR READ_ONLY FOR SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') AND -- exclude these databases recovery_model_desc = 'FULL' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN ALTER DATABASE [@name] SET RECOVERY SIMPLE; FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor