Interrogation des traces historiques dans SQL Server

Pour récupérer la date du dernier démarrage du service SQL Server, interrogez la vue de gestion dynamique :

SELECT sqlserver_start_time AS heure_demarrage FROM sys.dm_os_sys_info;

Des alternatives incluent l'examen du temps de création de la session système ou de la base de données tempdb :

SELECT login_time AS connexion_initiale FROM sys.dm_exec_sessions WHERE session_id = 1;

SELECT create_date AS date_creation_tempdb FROM sys.databases WHERE database_id = 2;

Pour visualiser les démarrages antérieurs, analysez les journaux d'erreurs via la procédure stockée :

DECLARE @fichier INT = 0;
WHILE @fichier <= 6
BEGIN
    EXEC xp_readerrorlog @fichier, 1, N'SQL Server is starting';
    SET @fichier = @fichier + 1;
END

Les événements de démarrage plus anciens se trouvent dans les journaux Windows, accessibles via l'Observateur d'événements sous Application ou Système.

Traçage des connexions

Par défaut, seules les tentatives de connexion échouées sont consignées dans le journal d'erreurs. Pour inclure les succès, activez l'option d'audit via la modification du registre :

USE master;
GO
EXEC xp_instance_regwrite 
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
    @value_name = N'AuditLevel',
    @type = REG_DWORD,
    @value = 3; -- 3 pour enregistrer à la fois les échecs et les succès
GO

Recherchez ensuite les entrées de connexion dans le journal d'erreurs :

EXEC xp_readerrorlog 0, 1, N'Login', N'for user', NULL, NULL, N'DESC';

Depuis SQL Server 2005 SP2, un déclencheur LOGON permet de créer une table d'historique des connexions :

CREATE DATABASE HistoriqueConnexions;
GO
USE HistoriqueConnexions;
GO
CREATE TABLE dbo.journal_connexions (
    id_connexion BIGINT IDENTITY(1,1) PRIMARY KEY,
    nom_login NVARCHAR(1024),
    heure_connexion DATETIME
);
GO
IF EXISTS (SELECT 1 FROM sys.server_triggers WHERE name = 'trg_suivi_connexions')
    DROP TRIGGER trg_suivi_connexions ON ALL SERVER;
GO
CREATE TRIGGER trg_suivi_connexions
ON ALL SERVER
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY\%' 
       AND ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE\%'
    BEGIN
        INSERT INTO HistoriqueConnexions.dbo.journal_connexions
        VALUES (ORIGINAL_LOGIN(), GETDATE());
    END;
END;
GO

Enregistrements des opérations DDL

Les dates de création et modification des objets serveur sont disponibles dans :

SELECT name, create_date AS date_creation FROM sys.databases;
SELECT name, create_date, modify_date FROM sys.server_principals;
SELECT name, create_date, modify_date FROM sys.server_triggers;

Pour les objets de base de données, utilisez :

SELECT name, create_date, modify_date FROM sys.objects;
SELECT name, create_date, modify_date FROM sys.triggers;

Notez que les index ne sont pas traqués pour ces opérations. La vue sys.dm_db_index_usage_stats fournit des statistiques d'utilisation post-démarrage du service :

SELECT obj.name AS nom_table,
       stats.last_user_seek,
       stats.last_user_scan,
       stats.last_user_update
FROM sys.indexes idx
LEFT JOIN sys.dm_db_index_usage_stats stats 
    ON idx.object_id = stats.object_id 
    AND idx.index_id = stats.index_id
INNER JOIN sys.objects obj 
    ON idx.object_id = obj.object_id
WHERE idx.index_id <= 1 
    AND obj.is_ms_shipped = 0
ORDER BY obj.name;

Le suivi par défaut, activé par défaut depuis SQL Server 2005, enregistre les suppressions et modifications d'objets. Pour interroger les traces :

DECLARE @chemin_trace NVARCHAR(1024);
SELECT @chemin_trace = path FROM sys.traces WHERE id = 1;

SELECT * 
FROM fn_trace_gettable(@chemin_trace, DEFAULT)
WHERE DatabaseName = 'MaBase' 
    AND EventClass IN (46, 47, 164) -- Création, suppression, modification
    AND ObjectType = 16964; -- Base de données

Historique des sauvegardes et restaurations

Les métadonnées des sauvegardes sont stockées dans msdb :

SELECT 
    bkp.database_name AS base_donnees,
    bkp.backup_start_date AS debut_sauvegarde,
    bkp.backup_finish_date AS fin_sauvegarde,
    CONVERT(VARCHAR(10), bkp.backup_size / 1048576) + ' Mo' AS taille,
    DATEDIFF(SECOND, bkp.backup_start_date, bkp.backup_finish_date) AS duree_secondes,
    CASE bkp.type 
        WHEN 'D' THEN 'Complète'
        WHEN 'I' THEN 'Différentielle'
        WHEN 'L' THEN 'Journal'
        ELSE 'Autre'
    END AS type_sauvegarde,
    media.physical_device_name AS chemin_fichier
FROM msdb.dbo.backupset bkp
INNER JOIN msdb.dbo.backupmediafamily media 
    ON bkp.media_set_id = media.media_set_id
ORDER BY bkp.database_name, bkp.backup_start_date;

Pour les restaurations :

SELECT 
    restauration.restore_date AS date_restauration,
    restauration.destination_database_name AS base_cible,
    media.physical_device_name AS fichier_source,
    CASE restauration.restore_type
        WHEN 'D' THEN 'Base'
        WHEN 'L' THEN 'Journal'
        ELSE 'Autre'
    END AS type_restauration
FROM msdb.dbo.restorehistory restauration
INNER JOIN msdb.dbo.backupset bkp 
    ON restauration.backup_set_id = bkp.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily media 
    ON bkp.media_set_id = media.media_set_id;

Suivi des tâches et courriels de base de données

L'historique des jobs se trouve dans msdb. Pour récupérer la dernière exécution de chaque job :

WITH DerniereExecution AS (
    SELECT 
        job_id,
        run_status,
        run_date,
        run_time,
        run_duration,
        ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY instance_id DESC) AS rang
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0
)
SELECT 
    jobs.name AS nom_job,
    CASE derniere.run_status 
        WHEN 0 THEN 'Échec'
        WHEN 1 THEN 'Succès'
        WHEN 2 THEN 'En attente'
        WHEN 3 THEN 'Annulé'
        ELSE 'Inconnu'
    END AS statut,
    CONVERT(DATETIME, 
        STUFF(STUFF(CAST(derniere.run_date AS CHAR(8)), 7, 0, '-'), 5, 0, '-') + ' ' +
        STUFF(STUFF(RIGHT('0' + CAST(derniere.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
    ) AS date_execution
FROM DerniereExecution derniere
INNER JOIN msdb.dbo.sysjobs jobs 
    ON derniere.job_id = jobs.job_id
WHERE derniere.rang = 1 AND jobs.enabled = 1;

Les logs de messagerie de base de données sont accessibles via :

SELECT sent_date, recipients, subject, sent_status 
FROM msdb.dbo.sysmail_allitems;

Examen des fichiers jouranux de transactions

La fonction fn_dblog permet d'inspetcer le journal de transactions en ligne :

SELECT 
    AllocUnitName AS nom_objet,
    Operation AS type_operation,
    [Log Record Length] AS longueur_enregistrement
FROM fn_dblog(NULL, NULL)
WHERE AllocUnitName IS NOT NULL;

Pour analyser les journaux dans une sauvegarde, utilisez fn_dump_dblog avec des paramètres explicites :

SELECT * 
FROM fn_dump_dblog(
    NULL, NULL, N'DISK', 1, N'C:\Sauvegarde\journal.trn',
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
);

Ces fonctions offrent une granularité fine mais requièrent une interprétation des colonnes binaires et des codes d'opération.