MCITP

MCITP

Wednesday, February 27, 2013

Script to shrink all DB's with in SQL server 2005.


SET NOCOUNT ON

DECLARE @DBName varchar(100)
DECLARE @FileName varchar(100)
DECLARE @TempTxt varchar(1000)
DECLARE @Cnt smallint

SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'

DECLARE cmdtxt_cursor CURSOR FOR SELECT  RTRIM(master..Sysdatabases.Name) as [DBName],
         RTRIM(master..Sysaltfiles.Name) as [FileName]
FROM  master..Sysdatabases
Inner join master..Sysaltfiles on master..Sysaltfiles.DBID = master..Sysdatabases.DBID
WHERE   master..Sysdatabases.DBID not in (1,2,3,4) and Patindex('%log%', master..Sysaltfiles.Name) > 0
and ((master..Sysaltfiles.Size)*8/1024) > 0 and Patindex ('% %', master..Sysdatabases.Name) = 0
ORDER BY Left(master..Sysaltfiles.FileName, 1)

SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)

OPEN cmdtxt_cursor
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Processing : ' + @DBName
If Exists(Select master..Sysdatabases.[Name] From master..Sysdatabases
           Where master..Sysdatabases.[Name] = @DBName)
BEGIN
SET @TempTxt = 'USE [' + @DBName + '];' + CHAR(13) +
'BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY ;' + CHAR(13) +
'DBCC SHRINKFILE (''' + @FileName + ''', 1);'
--PRINT @TempTxt
EXEC (@TempTxt)
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName
END
END

CLOSE cmdtxt_cursor
DEALLOCATE cmdtxt_cursor
SET @TempTxt = 'USE Master;' + CHAR(13) +
'EXEC xp_fixeddrives;'
--PRINT @TempTxt
EXEC (@TempTxt)
GO

No comments:

Post a Comment

Followers