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
Sources:
- https://msdn.microsoft.com/en-us/library/ms189272(v=sql.120).aspx
- https://dba.stackexchange.com/questions/6996/how-do-i-truncate-the-transaction-log-in-a-sql-server-2008-database
- http://www.connectsql.com/2012/08/sql-server-applying-filter-on.html
- https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/