Pour obtenir un bon niveau de détail lors de la vérification des structures d’allocation des espaces de stockage d’une base de données, il est nécessaire de passer les commandes une à une sur les différentes tables… Voici une procédure qui automatise cela…
La procédure suivante exécute la vérification des structures de stockage pour :
a) les données systèmes (CHECK CATALOG)
b) l’espace disque (CHECK ALLOC)
c) pour toutes les tables, une à une (CHECK TABLE)
Avec comme paramètre le nom de la base…
IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME = 'P_ADM_MAINTENANCE_PAGES')
DROP PROCEDURE dbo.P_ADM_MAINTENANCE_PAGES
GO
-- création procédure de maintenance des structures d'allocations
CREATE PROCEDURE dbo.P_ADM_MAINTENANCE_PAGES
@NOM_BASE SYSNAME, -- nom de la base à vérifier
@FILETEXTPATH VARCHAR(128) = NULL, -- emplacement pour les fichiers de trace. Si Null, ne trace rien
@TRACE BIT = 0, -- trace les actions entreprises par print
@EXEC BIT = 1 -- exécute si 1, sinon n'exécute pas
AS
SET NOCOUNT ON;
DECLARE @FILE_NAME NVARCHAR(256), -- nom du fichier
@F_HANDLE INT, -- handle OS du fichier
@F_ID INT, -- identifiant numérique du fichier
@SQL NVARCHAR(4000), -- commande SQL
@TBL VARCHAR (260), -- nom de table
@ERRNUM INT, -- n° de l'erreur
@ERRMSG VARCHAR(1024), -- message d'erreur
@ISERROR BIT -- mis à 1 en cas d'erreur
SET @NOM_BASE = COALESCE(@NOM_BASE, DB_NAME());
-- ouverture du fichier en écriture
IF @FILETEXTPATH IS NOT NULL
BEGIN
-- constitution du nom du fichier
SET @FILE_NAME = @FILETEXTPATH + 'P_ADM_MAINTENANCE_PAGES_' + @NOM_BASE + '_'
+ REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CURRENT_TIMESTAMP, 121), '-', ''), ' ', '_'), ':', '')
+ '.TXT';
EXEC dbo.P_A_FILE_OPEN_REWRITE @FILE_NAME, @F_HANDLE OUTPUT, @F_ID OUTPUT;
END;
SET @SQL = 'DBCC CHECKCATALOG ( ''' + @NOM_BASE + ''' );'
IF @TRACE = 1 PRINT @SQL;
IF @EXEC = 1
BEGIN
IF @FILETEXTPATH IS NOT NULL
BEGIN
SET @ERRMSG = '*** DEBUT CHECKCATALOG : ' + CONVERT(CHAR(19), CURRENT_TIMESTAMP, 121);
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @ERRMSG;
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @SQL; -- écriture texte de la commande
END
EXEC (@SQL);
SET @ERRNUM = @@ERROR;
IF @FILETEXTPATH IS NOT NULL
BEGIN
IF @ERRNUM <> 0
BEGIN
SET @ISERROR = 1;
SELECT @ERRMSG = '--> ' + description
FROM sysmessages
WHERE error = @ERRNUM
AND msglangid = 1036;
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @SQL; -- écriture texte de l'erreur
END
ELSE
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, '';
END
END
SET @SQL = 'DBCC CHECKALLOC ( ''' + @NOM_BASE + ''' );'
IF @TRACE = 1 PRINT @SQL;
IF @EXEC = 1
BEGIN
IF @FILETEXTPATH IS NOT NULL
BEGIN
SET @ERRMSG = '*** DEBUT CHECKALLOC : ' + CONVERT(CHAR(19), CURRENT_TIMESTAMP, 121);
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @ERRMSG;
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @SQL; -- écriture texte de la commande
END
EXEC (@SQL);
SET @ERRNUM = @@ERROR;
IF @FILETEXTPATH IS NOT NULL
BEGIN
IF @ERRNUM <> 0
BEGIN
SET @ISERROR = 1;
SELECT @ERRMSG = '--> ' + description
FROM sysmessages
WHERE error = @ERRNUM
AND msglangid = 1036;
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @SQL; -- écriture texte de l'erreur
END
ELSE
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, '';
END
END
CREATE TABLE #PAGE_TABLES
( table_name VARCHAR (260));
SET @SQL = 'SELECT ''['' + s.name + ''].['' + o.name +'']'' FROM '
+ @NOM_BASE + '..sysobjects AS o INNER JOIN '
+ @NOM_BASE + '..sysusers AS s ON o.uid = s.uid '
+ ' WHERE xtype = ''U'';';
INSERT INTO #PAGE_TABLES
EXEC(@SQL);
DECLARE C CURSOR
LOCAL READ_ONLY
FOR SELECT table_name
FROM #PAGE_TABLES;
OPEN C;
FETCH C INTO @TBL;
IF @FILETEXTPATH IS NOT NULL
BEGIN
SET @ERRMSG = '*** DEBUT CHECKTABLE : ' + CONVERT(CHAR(19), CURRENT_TIMESTAMP, 121);
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @ERRMSG;
END
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'DBCC CHECKTABLE (''' + @NOM_BASE +'.' + @TBL +''');'
IF @TRACE = 1 PRINT @SQL;
IF @EXEC = 1
BEGIN
IF @FILETEXTPATH IS NOT NULL
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @SQL; -- écriture texte de la commande
EXEC (@SQL);
SET @ERRNUM = @@ERROR;
IF @FILETEXTPATH IS NOT NULL
BEGIN
IF @ERRNUM <> 0
BEGIN
SET @ISERROR = 1;
SELECT @ERRMSG = '--> ' + description
FROM sysmessages
WHERE error = @ERRNUM
AND msglangid = 1036;
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @SQL; -- écriture texte de l'erreur
END
ELSE
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, '';
END
END
FETCH C INTO @TBL;
END
CLOSE C;
DEALLOCATE C;
IF @FILETEXTPATH IS NOT NULL
BEGIN
SET @ERRMSG = '*** FIN DES OPÉRATIONS : ' + CONVERT(CHAR(19), CURRENT_TIMESTAMP, 121);
EXEC dbo.P_A_FILE_WRITE_LINE @F_ID, @ERRMSG;
EXEC dbo.P_A_FILE_CLOSE @F_HANDLE, @F_ID -- fermeture fichier de trace
END
IF @ISERROR = 1
RAISERROR('Erreur dans la procédure de vérification d''intégrité physique pour la base %s.', 16, 1, @NOM_BASE);
GO
-- exemple lancement :
-- EXECUTE dbo.P_ADM_MAINTENANCE_PAGES 'msdb', 'C:\SQLERRORS\'
Attention : cette procédure utilise les procédures de manipulation des fichiers texte telles que décrite ici : Ecriture de fichiers texte via SQL
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
