[MSSQL] Onderhoud op alle indexen in alle databases op 1 svr

[Gé Brander]

Ik ben een beetje de weg kwijt:

code:

SELECT [O].[name]
    FROM [sysobjects] [O]
    , [sysindexes] [I] 
    WHERE [O].[type] IN ('U') 
    AND [O].[id] = [I].[id] 
    AND [I].[indid] IN (0, 1) 
    AND [O].[type] != 'S'

Dit select statement wil ik in elke database op mijn instance uit kunnen voeren en vervolgens op elk individueel resultaat de volgende opdracht uitvoeren.

code:

DBCC REINDEX (@tbl_nam, '', 70)

Nu heb ik wel binnen een sp een cursor geschreven waarin elke database geselecteerd wordt en vervolgens een andere sp aangeroepen wordt waarmee bovenstaand select statement uitgevoerd wordt, maar ik zit met het probleem dat ik steeds alleen maar de tabellen van de database terugkrijg waar de sp zich in bevindt. Op zich ook logisch. Maar hoe los ik dit nu op. Je kan binnen een SP geen 'use database' opdracht geven, dus hoe kan ik dan dynamisch selecteren binnen een sp naar steeds een andere database voor de sysobjects en sysindexes tabel.

De code die ik tot nu toe heb, maar fout gaat:

code:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[_SP_dbb_dbs_rdx] 
    @dbs_nam varchar(25)
AS

SET NOCOUNT ON

DECLARE @TBL_NAM AS VARCHAR(512)    -- Tabel naam
DECLARE @OPD AS VARCHAR(1024)       -- Opdracht

USE @dbs_nam

DECLARE TBL_CUR CURSOR FOR 

    SELECT [O].[name]
        FROM [sysobjects] [O]
        , [sysindexes] [I] 
        WHERE [O].[type] IN ('U') 
        AND [O].[id] = [I].[id] 
        AND [I].[indid] IN (0, 1) 
        AND [O].[type] != 'S' 

OPEN TBL_CUR

FETCH NEXT FROM TBL_CUR INTO @TBL_NAM

WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @OPD = 'DBCC DBREINDEX(' + @TBL_NAM + ', '''', 70)'

    PRINT @OPD

    EXEC (@OPD)

FETCH NEXT FROM TBL_CUR INTO @TBL_NAM

END

CLOSE TBL_CUR

DEALLOCATE TBL_CUR
    

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

[Annie]

Heb zelf ooit 's zoiets in elkaar geprutst door gebruik te maken van sp_msforeachdb (system stored proc). Zal 's voor je kijken of ik deze nog terug kan vinden.

/edit: gevonden

Ik heb een reindex-proc aangemaakt in master database

code:

USE master
GO

CREATE PROC sp_reindex_alltables 
  @fillfactor INT = 90 -- fillfactor of index (percentage)
AS
PRINT '*********************************************************************************'
PRINT 'REINDEXING DATABASE : ' + DB_NAME()
PRINT '*********************************************************************************'

DECLARE @tablename SYSNAME, @stmt VARCHAR(1000)
SET @stmt = 'DECLARE TableCursor CURSOR FOR
  SELECT TABLE_NAME 
  FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = ''base table''
'
EXEC (@stmt)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
  -- check to see if table contains an index
  IF EXISTS(
            SELECT indid FROM sysindexes
            WHERE id = OBJECT_ID(DB_NAME() + '..' + @tablename) 
              AND indid > 0 AND indid < 255 
              AND (status & 64) = 0
           )
    BEGIN -- table is indexed
      PRINT 'Reindexing ' + @tablename
      SET @tablename = DB_NAME() + '..' + @tablename
      DBCC DBREINDEX(@tablename, '', @fillfactor)
    END
  ELSE
    BEGIN
      PRINT @tablename + ' does not have any indexes.'
    END

  PRINT '---------------------------------------------------------------------------------'

  FETCH NEXT FROM TableCursor INTO @tablename
END

PRINT ''

CLOSE TableCursor

DEALLOCATE TableCursor
GO
GRANT EXEC ON sp_reindex_alltables TO [public]
GO

En vervolgens deze uitgevoerd voor elke database

code:

EXEC sp_msforeachdb 'USE [?] EXEC sp_reindex_alltables'

Probleempje: alleen tables van dbo worden geherindexeerd. Maar daar moet ook nog wel wat voor te vinden zijn lijkt me.

[ Voor 93% gewijzigd door Annie op 31-08-2004 17:50 ]

[Annie]

Maar waarom maak je niet gewoon een database maintenance plan aan? Daarmee kan je ook herindexeren en alle user databases kiezen. Lijkt me een stuk makkelijker.

[Gé Brander]

Annie:

Ik hou niet van die automatische dingen die ik niet zelf in de hand heb zoals een maintenance plan. Ik regel liever zelf die zaken met de opties die ik wil hebben.

Maar in ieder geval bedankt voor de tip, die ga ik morgen even proberen.

Maar heb ik met

code:

 EXEC sp_msforeachdb 'USE [?] EXEC sp_reindex_alltables'

juist nog steeds niet mijn probleem opgelost volgens mij. Je moet nog steeds voor elke database bovenstaand commando uitvoeren met een variabele @database.
Ik was juist op zoek naar een methode om automatisch elke database die er maar bestaat te reindexen. Zo moet je de code hierboven alsnog per database uitvoeren en gaat het dus niet automatisch en is de kans op vergeten groot... Of heb ik de code niet helemaal goed begrepen?

[ Voor 54% gewijzigd door Gé Brander op 31-08-2004 19:40 ]

[Annie]

c70070540 schreef op 31 augustus 2004 @ 19:34:
Ik hou niet van die automatische dingen die ik niet zelf in de hand heb zoals een maintenance plan. Ik regel liever zelf die zaken met de opties die ik wil hebben.

Tja, jammer dat je er zo over denkt. Zo mis je een heleboel gemak van de tools die MSSQL je biedt.

c70070540 schreef op 31 augustus 2004 @ 19:34:
Of heb ik de code niet helemaal goed begrepen?

Dat dus

[Gé Brander]

Annie schreef op 31 augustus 2004 @ 21:44:
[...]

Tja, jammer dat je er zo over denkt. Zo mis je een heleboel gemak van de tools die MSSQL je biedt.

Het bied inderdaad gemak, maar ik kan er niet genoeg in sturen, ik kan ze niet automatisch aanmaken op een makkelijke manier maar ook de history van de jobs is maximaal 500 rijen in de MSDB. Dit kan natuurlijk uitgebreid worden, maar ik heb zelf het een en ander geschreven dat het onderhoud van de database verzorgd, waarbij ook vrije schijfruimte, server configuratie etc bijgehouden wordt.

[...]

Dat dus

Ok... Ik ga nog eens met wakkere ogen er naar kijken