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

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

DECLARE @SQL  varchar(8000) -- if you use nvarchar for whack table names, change this to 4000
SET @SQL = ''
SELECT @SQL = @SQL + 'ALTER DATABASE ' + NAME + ' SET RECOVERY SIMPLE; ALTER DATABASE ' + NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DBCC SHRINKDATABASE (' + NAME + '); ALTER DATABASE ' + NAME + ' SET MULTI_USER' + Char(13)
FROM MASTER..Sysdatabases where sid != 0x01

PRINT @SQL
EXEC (@SQL)

This code does the following to all non-system databases:
1. Sets the recover 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

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

*