USE [master];



DECLARE @dbName VARCHAR (100);

DECLARE @partitions bigint;

DECLARE @strQery VARCHAR (500);

DECLARE @tmpIndDB VARCHAR (10);

SET @tmpIndDB = 'indexTmpDB';



IF EXISTS (SELECT name FROM sys.objects WHERE name = @tmpIndDB)

   DROP TABLE indexTmpDB;



SELECT name

INTO indexTmpDB

FROM master.dbo.sysdatabases

WHERE name not in ('master', 'model', 'msdb', 'tempdb');



DECLARE partitions CURSOR FOR SELECT * FROM indexTmpDB;



OPEN partitions;



FETCH NEXT

   FROM partitions

   INTO @dbName;



WHILE @@FETCH_STATUS = 0

   BEGIN;

      PRINT @dbName;

      SET @strQery = 'USE [' + @dbName + '];

DBCC SHRINKFILE (2, 100);';

      EXEC  (@strQery);

      FETCH NEXT FROM partitions INTO @dbName;

   END;

CLOSE partitions;

DEALLOCATE partitions;

--

-- SHRINK DATABASE

--

-- Last edition: 22/06/2010

--

USE

[DAM2009]

ALTER

DATABASE DAM2009 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- MULTI_USER -- SINGLE_USER -- MULTI_USER -- SINGLE_USER

GO

DBCC

CHECKDB ('DAM2009',REPAIR_REBUILD)

GO

BACKUP

GO

LOG DAM2009 WITH TRUNCATE_ONLY

DBCC

SHRINKFILE(DAM2009, 8)

DBCC

SHRINKFILE(DAM2009_log, TRUNCATEONLY) -- 4

DBCC

SHRINKDATABASE(DAM2009, TRUNCATEONLY )

--DBCC SQLPERF(LOGSPACE)

--DBCC OPENTRAN()

GO

ALTER

DATABASE DAM2009 SET MULTI_USER -- SINGLE_USER -- SINGLE_USER -- MULTI_USER -- SINGLE_USER

GO

GO