1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
|
create procedure _xReducTailleBDD
/**********************************************************************
Type d'objet : Procédure stockée
Domaine : Administration/Tunning SQL Serveur
Objectif : Réduit la taille des journaux et base de données de toutes les bdd du serveur
Date : 20/06/2006
Auteur : Raoul CHRISMANN
**********************************************************************/
as
begin
declare
@dbName varchar(254),
@cmd00 varchar(100),
@cmd01 varchar(50),
@cmd02 varchar(50),
@cmd20 varchar(100),
@cmd21 varchar(50),
@cmd22 varchar(50)
select @cmd01 = 'dump tran '
select @cmd02 = ' with no_log'
select @cmd21 = 'DBCC SHRINKDATABASE ('
select @cmd22 = ',10)'
declare curMaint cursor local forward_only for
select name
from master.dbo.sysdatabases
-- bdd à exclure du traitement
where name not in ('master','tempdb','model','msdb','pubs','Northwind')
-- Affichage des informations avant traitement
DBCC SQLPERF(LOGSPACE)
open curMaint
fetch next from curMaint into @dbName
while @@FETCH_STATUS = 0
begin
--select @dbName
/* dump tran @dbName with no_log */
select @cmd00 = @cmd01 + @dbName + @cmd02
--select @cmd00
exec (@cmd00)
/* log file compression = DBCC SHRINKDATABASE (@dbName,10) */
select @cmd20 = @cmd21 + rtrim(@dbName) + @cmd22
--select @cmd20
exec(@cmd20)
fetch next from curMaint into @dbName
end
close curMaint
deallocate curMaint
-- Affichage des informations après traitement
DBCC SQLPERF(LOGSPACE)
end |
Partager