About Me

My photo
a Dynamic and Energetic guy.....
Showing posts with label Shrink Databases. Show all posts
Showing posts with label Shrink Databases. Show all posts

Thursday, September 27, 2012

Shrink All Databases In SQL 2008 Using A Query

IF EXISTS(select * from sysobjects where id = object_id('dbo.SP_ShrinkAllDatabasesOnServer') and xtype = 'P')
DROP PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
GO
CREATE PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
AS
BEGIN
CREATE TABLE #TempDatabasesTable
(
[DatabaseName] sysname not null primary key,
Mod tinyint not null default 1
)
INSERT INTO #TempDatabasesTable ([DatabaseName])
SELECT
name
FROM
master..sysdatabases
WHERE
dbid > 4
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
print '*******************************************************************'
print '> DB: ' + @DatabaseName
print '> SET RECOVERY MODE SIMPLE'
declare @SqlCommand nvarchar(4000)
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
DROP TABLE #TempDatabasesTable
END
GO
exec dbo.SP_ShrinkAllDatabasesOnServer

My Masters