begin process at 2008 07 06 07:04:06
1 205 472 membres
51 nouveaux aujourd'hui
14 119 membres club

Vous ne trouvez pas de réponse à votre problème ? Alors posez la question dans le forum.
Souvenez-vous qu'il n'y a jamais de question bête, mais rester dans l'ignorance parce que l'on n'ose pas poser une question, ça c'est une erreur !

SQL SERVER - PROCÉDURE STOCKÉE PERMETTANT DE DEFRAGMENTER TOUS LES INDEXES DE TOUTES LES BASES DU SERVEUR


Information sur la source

Catégorie :Divers Classé sous : dbcc, indexes, défragmentation Niveau : Expert Date de création : 20/03/2006 Date de mise à jour : 03/08/2007 10:58:05 Vu : 3 511

Note :
10 / 10 - par 1 personne
10,00 / 10

  • 1

  • 2

  • 3

  • 4

  • 5

  • 6

  • 7

  • 8

  • 9

  • 10

Commentaire sur cette source (0)
Ajouter un commentaire et/ou une note


Description

Comme la source précédente :
- http://sqlfr.com/code.aspx?ID=35836

Il y a des cas (system 24*7 par exemple), où la reconstruction des indexes est impossible. En effet, cette reconstruction commence par la suppression des indexes existant. Ainsi, cela peut pénaliser créellement les utilisateurs à ce moment.
Il est donc plus simple de faire une défragmentation des Indexes existant.

Voila donc la Procédure stockée faisant cela.

Source

  • SET QUOTED_IDENTIFIER ON
  • GO
  • SET ANSI_NULLS ON
  • GO
  • ALTER PROCEDURE dbo.SP_System_DefragdAllIndexes
  • /*
  • Permet de défragmentation des Indexes
  • de toutes les bases du serveur SQL Server
  • */
  • AS
  • DECLARE @name sysname
  • DECLARE @Objectname sysname
  • DECLARE @Username sysname
  • DECLARE @IndexName sysname
  • DECLARE @LaRequette varchar(8000)
  • DECLARE @DateJour varchar(20)
  • SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_')
  • PRINT '---------------------------------------------------------------------'
  • PRINT ' DATE DE LA MISE A JOUR DES INDEXES LANCEE : '+ @DateJour
  • PRINT '---------------------------------------------------------------------'
  • DECLARE TESTCURSEUR CURSOR
  • FOR SELECT Master.dbo.sysdatabases.name FROM Master.dbo.sysdatabases
  • WHERE Master.dbo.sysdatabases.name NOT IN ('tempdb', 'model', 'pubs', 'master', 'msdb', 'LVM')
  • ORDER BY Master.dbo.sysdatabases.name
  • OPEN TESTCURSEUR
  • FETCH NEXT FROM TESTCURSEUR
  • INTO @name
  • WHILE @@FETCH_STATUS = 0
  • BEGIN
  • PRINT ''
  • PRINT '---------------------------------------------------------------------'
  • PRINT ' DEFRAGMENTATION DES INDEXES DE LA BASE : '+ @name
  • PRINT '---------------------------------------------------------------------'
  • SET @LaRequette = 'SELECT USR.name AS UserName, O.name AS Objectname, IDX.name IndexName
  • FROM '+ @name +'.dbo.sysobjects O INNER JOIN '+ @name +'.dbo.sysusers USR
  • ON O.uid = USR.uid INNER JOIN '+ @name +'.dbo.sysindexes IDX
  • ON O.id = IDX.id WHERE (O.xtype= ''U'' OR O.xtype= ''V'')
  • AND NOT (IDX.keys IS NULL) AND NOT (IDX.status & 64 = 64);'
  • PRINT 'Changement de base : '+ @LaRequette
  • PRINT '---------------------------------------------------------------------'
  • EXEC('DECLARE TESTCURSEURTABLE CURSOR FOR '+ @LaRequette)
  • OPEN TESTCURSEURTABLE
  • FETCH NEXT FROM TESTCURSEURTABLE
  • INTO @Username, @Objectname, @IndexName
  • WHILE @@FETCH_STATUS = 0
  • BEGIN
  • SET @LaRequette = 'DBCC INDEXDEFRAG ('''+ @name +''', '''+ @Username+'.'+ @Objectname +''', '''+ @IndexName +''')'
  • PRINT 'Requette : '+ @LaRequette
  • EXECUTE (@LaRequette)
  • PRINT '---------------------------------------------------------------------'
  • FETCH NEXT FROM TESTCURSEURTABLE
  • INTO @Username, @Objectname, @IndexName
  • END
  • CLOSE TESTCURSEURTABLE
  • DEALLOCATE TESTCURSEURTABLE
  • SET @LaRequette = 'USE '+ @name +' exec sp_updatestats'
  • PRINT 'Requette : '+ @LaRequette
  • EXECUTE (@LaRequette)
  • FETCH NEXT FROM TESTCURSEUR
  • INTO @name
  • END
  • PRINT '---------------------------------------------------------------------'
  • PRINT ' FIN DE LA DEFRAGMENTATION
  • PRINT '---------------------------------------------------------------------'
  • CLOSE TESTCURSEUR
  • DEALLOCATE TESTCURSEUR
  • GO
  • SET QUOTED_IDENTIFIER OFF
  • GO
  • SET ANSI_NULLS ON
  • GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER   PROCEDURE dbo.SP_System_DefragdAllIndexes 
/* 
    Permet de défragmentation des Indexes 
     de toutes les bases du serveur SQL Server 
*/ 
   
AS 
  
DECLARE @name sysname 
DECLARE @Objectname sysname
DECLARE @Username sysname
DECLARE @IndexName sysname
DECLARE @LaRequette varchar(8000) 
DECLARE @DateJour varchar(20) 
   
SET @DateJour = REPLACE(CONVERT(VARCHAR, GetDate(), 102), '.', '_') 
PRINT '---------------------------------------------------------------------' 
PRINT ' DATE DE LA MISE A JOUR DES INDEXES LANCEE : '+ @DateJour 
PRINT '---------------------------------------------------------------------' 
   
DECLARE TESTCURSEUR CURSOR 
   FOR SELECT Master.dbo.sysdatabases.name FROM Master.dbo.sysdatabases 
       WHERE Master.dbo.sysdatabases.name NOT IN ('tempdb', 'model', 'pubs', 'master', 'msdb', 'LVM') 
	ORDER BY Master.dbo.sysdatabases.name
   
OPEN TESTCURSEUR 
FETCH NEXT FROM TESTCURSEUR 
INTO @name 
   
WHILE @@FETCH_STATUS = 0 
   
BEGIN 
    PRINT '' 
    PRINT '---------------------------------------------------------------------' 
    PRINT ' DEFRAGMENTATION DES INDEXES DE LA BASE : '+ @name 
    PRINT '---------------------------------------------------------------------' 
  
    SET @LaRequette = 'SELECT USR.name AS UserName, O.name AS Objectname, IDX.name IndexName
            FROM '+ @name +'.dbo.sysobjects O INNER JOIN '+ @name +'.dbo.sysusers USR 
		ON O.uid = USR.uid INNER JOIN '+ @name +'.dbo.sysindexes IDX
	    ON O.id = IDX.id WHERE (O.xtype= ''U'' OR O.xtype= ''V'')
	    AND NOT (IDX.keys IS NULL) AND NOT (IDX.status & 64 = 64);' 
  
    PRINT 'Changement de base : '+ @LaRequette 
    PRINT '---------------------------------------------------------------------' 
  
    EXEC('DECLARE TESTCURSEURTABLE CURSOR FOR '+ @LaRequette) 
    OPEN TESTCURSEURTABLE 
    FETCH NEXT FROM TESTCURSEURTABLE 
    INTO @Username, @Objectname, @IndexName 
   
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        SET @LaRequette = 'DBCC INDEXDEFRAG ('''+ @name +''', '''+ @Username+'.'+ @Objectname +''', '''+ @IndexName +''')' 
        PRINT 'Requette : '+ @LaRequette 
        EXECUTE (@LaRequette) 
        PRINT '---------------------------------------------------------------------' 
        FETCH NEXT FROM TESTCURSEURTABLE 
        INTO @Username, @Objectname, @IndexName
    END     
  
    CLOSE TESTCURSEURTABLE 
    DEALLOCATE TESTCURSEURTABLE 

    SET @LaRequette = 'USE '+ @name +' exec sp_updatestats' 
    PRINT 'Requette : '+ @LaRequette 
    EXECUTE (@LaRequette) 
  
    FETCH NEXT FROM TESTCURSEUR 
    INTO @name 
END 
  
PRINT '---------------------------------------------------------------------' 
PRINT ' FIN DE LA DEFRAGMENTATION 
PRINT '---------------------------------------------------------------------' 
  
CLOSE TESTCURSEUR 
DEALLOCATE TESTCURSEUR 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Conclusion

En espérant que cette SP vous rende service.

Bon coding

Romelard Fabrice (Alias F___)
20 mars 2006 20:27:57 :
Mise a jour en ajoutant un filtre sur les indexes de statistiques. Ajout de la mise a jour des stats en fin d'exécution. Remerciement à Frédéric BROUARD pour cette aide.
19 avril 2006 00:02:04 :
Correction du titre
03 août 2007 10:58:05 :
Mise à jour pour gestion des objets avec user spécifique
    Aucun commentaire pour le moment.

Ajouter un commentaire

Pub



Appels d'offres

Plugin Dialer outlook
Budget : 2 000€
Travail graphique- ill...
Budget : 1 000€
creation de marque et ...
Budget : 1 000€

Snippets en rapport

CalendriCode

Juillet 2008
LMMJVSD
 123456
78910111213
14151617181920
21222324252627
28293031   

Téléchargements

Logiciels à télécharger sur le même thème :

Boutique

Boutique de goodies CodeS-SourceS