samc technologies blogs

SAMC Technologies staff feel it's important to share our experiences with others as part of our open communication approach.  Please feel free to send us any comments you have regarding our blogs. 

SQL: Shrink file sizes down

Cursor to shrink file sizes down with 10% left free on all databases on a server.

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET ANSI_NULLS ON
DECLARE @DBName VARCHAR(100)
,@SQLString VARCHAR(255)
DECLARE DB_Cursor CURSOR
FOR
SELECT a.name
FROM master..sysdatabases a
OPEN DB_Cursor
FETCH NEXT
FROM DB_Cursor
INTO @DBName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @SQLString = 'DBCC SHRINKDATABASE ('+@DBName+', 10)'
EXEC (@SQLString)
END
FETCH NEXT
FROM DB_Cursor
INTO @DBName
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor