SQL Server: shrink all databases

EXEC sp_msForEachDB 'PRINT ''

USE ?

GO

ALTER DATABASE ? SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE(''''?'''',1)

GO

ALTER DATABASE ? SET RECOVERY FULL

GO''

'

SQL Server: shrink all transaction logs retrieving data from sysfiles

CREATE TABLE #LogFileResults (DatabaseName VARCHAR(150), LogFileName VARCHAR(150) )

DECLARE DataBaseList CURSOR FOR

SELECT name FROM SYS.sysdatabases

WHERE NAME NOT IN ('master','tempdb','model','msdb','distribution')

DECLARE @dbs VARCHAR(128)

DECLARE @cmd VARCHAR(MAX)

OPEN DataBaseList FETCH

NEXT FROM DataBaseList INTO @dbs

WHILE @@FETCH_STATUS <> -1

BEGIN

SET @cmd = 'USE [' + @dbs + '] INSERT INTO #LogFileResults(DatabaseName, LogFileName) SELECT '''

+ @dbs + ''', Name FROM sysfiles WHERE FileID=2'

EXEC(@cmd)

FETCH NEXT FROM DataBaseList INTO @dbs END

DEALLOCATE DataBaseList

DECLARE TransactionLogList CURSOR FOR

SELECT DatabaseName, LogFileName FROM #LogFileResults

DECLARE @LogFile VARCHAR(128)

OPEN TransactionLogList FETCH

NEXT FROM TransactionLogList INTO @dbs, @LogFile

WHILE @@FETCH_STATUS <> -1

BEGIN

SELECT @cmd = 'USE [' + @dbs + '] '

+ 'ALTER DATABASE [' + @dbs + '] SET RECOVERY SIMPLE WITH NO_WAIT '

+ 'DBCC SHRINKFILE(N''' + @LogFile + ''', 1) '

+ 'ALTER DATABASE [' + @dbs + '] SET RECOVERY FULL WITH NO_WAIT'

Print (@cmd)

FETCH NEXT FROM TransactionLogList INTO @dbs, @LogFile END

DEALLOCATE TransactionLogList

DROP TABLE #LogFileResults

MS SQL Server 2008 R2: Transaktionsprotokoll shrinken

Problem: Das Transaktionsprotokoll einer Datenbank läßt sich nicht verkleinern.

Lösung: Sichern des Transaktionsprotokolls mit TRUNCATE ONLY. Danach mit DBCC shrinken.

USE myDatabase
GO
DBCC SHRINKFILE(DatabaseTransactionLog, 1)
BACKUP LOG myDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(myDatabaseTransactionLog, 1)
GO

Ebenfalls bewährt hat sich folgende Vorgehensweise:

--set database in recovery model simple

alter database myDatabase set recovery simple

--backup database

backup database myDatabase to disk='C:\backups\myDatabase.bak' with init

--shrink transaction log file

dbcc shrinkfile ('myDatabase_log',10)

--set database in recovery model full

alter database myDatabase set recovery full

--backup database again

backup database myDatabase to disk='C:\backups\myDatabase.bak' with init