Programming Analytics

We make lots of databases here at the office.  Often they’re play databases, or restore databases, or test databases.  Since they rarely progress from development status to production status, they need to be set up with simple recovery model so they don’t collapse under the weight of the dozens of gigabytes of transaction logs my programmers pump in every day (at least, until Western Digital’s factory comes back online and we start buying hard drives again).

I was annoyed that my scripts were hardcoded to shrink transaction logs on a database by database basis.  This meant that anytime a programmer added a new database and didn’t tell me, that one could grow and fill up the disk.  So instead I wrote a single stored proc that resolves all transaction logs at one go:

DECLARE @Script nvarchar(max)

SELECT @Script = @Script + ‘USE ’ + QUOTENAME(d.name) + ‘;

DBCC SHRINKFILE (’ + QUOTENAME(f.name) + ‘); 

‘

  FROM sys.databases d

       INNER JOIN sys.master_files f ON f.database_id = d.database_id

 WHERE d.recovery_model_desc = ‘SIMPLE’

   AND d.state_desc = ‘ONLINE’

   AND f.type_desc = ‘LOG’

   AND d.name <> ‘master’

   AND d.name <> ‘tempdb’

   AND d.name <> ‘model’

   AND d.name <> ‘msdb’

IF (@@ROWCOUNT > 0) BEGIN

 PRINT @Script

 EXEC (@Script)

END

Why does this work?  First, we use the SQL server internal data structures to identify databases with simple recovery which are currently online.  Then we exclude the system databases which shouldn’t be part of this process, and prepare a SQL statement to call DBCC SHRINKFILE() on each developer’s database.  SQL provides the nifty ‘EXEC’ ability to run that query - and you’re off to the races!