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
@sql
EXEC
(@sql)
Advertisements