TSQL: shrink all databases on SQL SERVER

 

IF EXISTS(select * from sysobjects where id = object_id(‘dbo.spShrinkAllDatabasesOnServer’) and xtype = ‘P’)
DROP PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
GO

 

CREATE PROCEDURE [dbo].[spShrinkAllDatabasesOnServer]
AS
BEGIN

 
CREATE TABLE #TempDatabasesTable

 

(
[DatabaseName] sysname not null primary key,
Mod tinyint not null default 1
)

 

INSERT INTO #TempDatabasesTable ([DatabaseName])
SELECT name FROM sys.databases WHERE database_id not in (1,2,3,4,5,6,7,8,9,94) order by database_id

 

 

 

DECLARE @DatabaseName sysname
SET @DatabaseName = ”
WHILE @DatabaseName is not null
BEGIN

 

SET @DatabaseName = NULL

 

SELECT TOP 1 @DatabaseName = [DatabaseName] from #TempDatabasesTable where Mod = 1

 

IF @DatabaseName is NULL BREAK

 
–wenn keine offenen Connections dann verkleinern
IF isnull((SELECT count(dbid) FROM sys.sysprocesses WHERE db_name(dbid)= @DatabaseName) , 0) = 0

 

BEGIN
PRINT ‘ ‘
PRINT ‘ ‘
PRINT ‘*******************************************************************’
PRINT ‘> DB: ‘ + @DatabaseName

 

DECLARE @SqlCommand nvarchar(4000)

EXEC(
‘ USE ‘ + @DatabaseName +
‘ EXEC(”
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR LOCAL FOR
SELECT table_name FROM information_schema.tables WHERE table_type = ””base table””

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Print ””Reindexing Table: ”” + @TableName
DBCC DBREINDEX(@TableName,”” ”” , 90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
”)’)

PRINT ‘> SET RECOVERY MODE SIMPLE’

SET @SqlCommand = ‘ALTER DATABASE [‘ + @DatabaseName + ‘] SET recovery simple’
EXEC sp_executesql @SqlCommand

 

PRINT ‘> Shrinking database’

 

SET @SqlCommand = ‘dbcc shrinkdatabase([‘ + @DatabaseName + ‘])’
EXEC sp_executesql @SqlCommand

 

UPDATE #TempDatabasesTable set Mod = 0 where [DatabaseName] = @DatabaseName
END

END

 

DROP TABLE #TempDatabasesTable

 

END

 

Advertisements

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