A Tech Log

April 1, 2009

TSQL to Shrink All Databases on a Server

Filed under: Development — adallow @ 3:44 pm
Tags: ,
DECLARE @name varchar(500)
DECLARE @sql varchar(8000)
SET @sql = ”
DECLARE Database_Cursor CURSOR READ_ONLY FOR
SELECT Name
FROM sysdatabases
WHERE DBID > 4
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ‘backup log [' + @name + '] with truncate_only’ + CHAR(10)
SET @sql = @sql +  ‘dbcc SHRINKDATABASE ( [' + @name + '] )’ + CHAR(10)

FETCH NEXT FROM Database_Cursor INTO @name

END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor


print @sql

EXEC(@sql)

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: