Lösungen und Software-Entwicklung im Collaboration Umfeld, insbesondere Microsoft SharePoint, Microsoft Exchange und Microsoft Office.


Mobil: +49 (152) 53 97 78 79
Weitere Kontaktmöglichkeiten: Kontaktseite
Dekoration: Köln

Shrink all Databases
Created: 10.09.2012
Categories: SQL Server

      If you want to shrink all your databases including logs you can use the following script:
      declare @dbname sysname
      declare @sql nvarchar(1000)

      declare db_cursor cursor for
      select name from master.dbo.sysdatabases
      where name not in ('tempdb', 'model', 'msdb', 'master')
      open db_cursor

        fetch next from db_cursor into @dbname
        while @@fetch_status = 0
          print @dbname
          select @sql = '
          ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE
          DBCC SHRINKDATABASE ([' + @dbname + '])
          ALTER DATABASE [' + @dbname + '] SET RECOVERY FULL'
          exec sp_executesql @sql
          fetch next from db_cursor into @dbname

      close db_cursor
      deallocate db_cursor

      Caution: do not use the script in productive databases since you lose
      the logs for your current backup.

Send us a Comment!