T-SQL: Don't Run This in a Production Environment

calendar Feb 2, 2007  · Updated: November 3, 2025 · 1 min read  ·
Share on: linkedin bluesky copy

Here's some code that I'm posting mainly for my future reference. I do not recommend running this anywhere near a production machine.

 1DECLARE @SQL varchar(8000) -- if you use nvarchar for whack table names, change this to 4000
 2SET @SQL = ''
 3
 4SELECT @SQL = @SQL +
 5    'ALTER DATABASE ' + NAME + ' SET RECOVERY SIMPLE; ' +
 6    'ALTER DATABASE ' + NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' +
 7    'DBCC SHRINKDATABASE (' + NAME + '); ' +
 8    'ALTER DATABASE ' + NAME + ' SET MULTI_USER' + Char(13)
 9FROM master..sysdatabases
10WHERE sid != 0x01
11
12PRINT @SQL
13EXEC (@SQL)

This code does the following to all non-system databases:

  1. Sets the recovery model to SIMPLE (my log files were OOC).
  2. Kicks out all users and sets the DB to SINGLE_USER.
  3. Shrinks the database files.
  4. Sets the database back to MULTI_USER.

Note: This is a historical post from 2007. Objects like master..sysdatabases are deprecated in modern SQL Server; the contemporary catalog view is sys.databases. The caution still stands—do not run this in production.