Category Archives: Shrinking

Shrink all databases in SQL Server 2005


/*******  Shrink all databases in SQL Server 2005 using below script ******* /

DECLARE

@name varchar(500)

DECLARE

@sql varchar(8000)

SET

@sql =

DECLARE

Database_Cursor CURSOR READ_ONLY FORSELECT

 NameFROM

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)

%d bloggers like this: