Occasionally I will be working on a project where we have some database operations that delete/update/insert large amounts of data by design. Every now and then we get caught by surprise when one of our development or QA servers runs low on disk space, and then we discovery that the log files for the databases are HUGE.
In most cases it turns out that we had the databases set to FULL RECOVERY MODE. You can find many articles about this on the Internet and on MSDN, but the bottom line is that for our purposes SIMPLE recovery mode is more than adequate and it will greatly reduce the size of these log files. Full recovery would allow us to roll the database back to point in time – generally we don’t need this, especially on our development and QA databases.
I have some cool backup/restore scripts and we simply added an ALTER DATABASE statement to end of the restore script to make sure that the database is in SIMPLE recovery mode. Then I got to thinking, why not just create a one-liner that will set all of the user databases on a given server to SIMPLE recovery mode? Well – here it is. Enjoy!
EXECUTE sp_msforeachdb 'IF ''?'' NOT IN (''tempdb'', ''master'', ''model'', ''msdb'') exec(''ALTER DATABASE [?] SET RECOVERY SIMPLE'')'
In case you are wondering why the ALTER above is in an EXEC, apparently SQL server will parser the ALTER statement and stop on tempdb without even executing the script, so even though tempdb would be excluded in the IF statement, SQL still sees the ALTER code and doesn’t like it. Turning this into a dynamic query avoids the problem.
No comments:
Post a Comment