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 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
| S_SYS dans msdb :
USE msdb;
GO
CREATE SCHEMA S_SYS
CREATE TABLE T_A_DISK_DSK
( DSK_ID INT NOT NULL PRIMARY KEY,
DSK_UNIT CHAR(1) NOT NULL UNIQUE CHECK (DSK_UNIT COLLATE French_CI_AS BETWEEN 'C' AND 'Z'),
DSK_ALERT_PC FLOAT NOT NULL DEFAULT 30.0 CHECK (DSK_ALERT_PC BETWEEN 0.0 AND 100.0))
CREATE TABLE T_A_TRACE_SPACE_DISK_TSP
( TSP_ID INT NOT NULL PRIMARY KEY,
DSK_UNIT CHAR(1) NOT NULL FOREIGN KEY REFERENCES T_A_DISK_DSK (DSK_UNIT),
TSP_DATETIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
TSP_SIZE_MO INT NOT NULL,
TSP_USED_MO INT NOT NULL);
GO
CREATE INDEX X_TSP_DTM ON S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_DATETIME, DSK_UNIT);
GO
Création de la procédure de capture des données d'espace disque
CREATE PROCEDURE S_SYS.P_AUDIT_SPACE_DISK
AS
SET NOCOUNT ON;
DECLARE @HDL int,
@FSO int,
@HD char(1),
@DRV int,
@SZ varchar(20),
@MB bigint ;
SET @MB = 1048576;
CREATE TABLE #HD (HD_UNIT char(1) PRIMARY KEY,
HD_FREESPACE int NULL,
HD_SIZE int NULL);
INSERT INTO #HD (HD_UNIT, HD_FREESPACE)
EXEC master.dbo.xp_fixeddrives;
DELETE FROM #HD
WHERE HD_UNIT NOT IN (SELECT DSK_UNIT
FROM S_SYS.T_A_DISK_DSK);
EXEC @HDL = sp_OACreate 'Scripting.FileSystemObject',@FSO OUT;
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
DECLARE C CURSOR LOCAL FAST_FORWARD
FOR SELECT HD_UNIT
FROM #HD;
OPEN C;
FETCH NEXT FROM C INTO @HD;
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @HDL = sp_OAMethod @FSO, 'GetDrive', @DRV OUT, @HD
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
EXEC @HDL = sp_OAGetProperty @DRV, 'TotalSize', @SZ OUT
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @DRV;
UPDATE #HD
SET HD_SIZE = CAST(@SZ AS FLOAT) / @MB
WHERE HD_UNIT = @HD;
FETCH NEXT FROM C INTO @HD;
END
CLOSE C;
DEALLOCATE C;
EXEC @HDL=sp_OADestroy @FSO;
IF @HDL <> 0 EXEC sp_OAGetErrorInfo @FSO;
INSERT INTO S_SYS.T_A_TRACE_SPACE_DISK_TSP (TSP_UNIT, TSP_SIZE_MO, TSP_USED_MO)
SELECT HD_UNIT, HD_SIZE, HD_SIZE - HD_FREESPACE
FROM #HD
DROP TABLE #HD;
RETURN;
GO
Mise en place dans l'agent SQL Server serveur d'une routine journalière de scrutation à 5h du matin
USE [msdb]
GO
EXEC msdb.dbo.sp_add_job
@job_name=N'Scrutation espace disque',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'Data Collector',
@owner_login_name=N'SA';
EXEC msdb.dbo.sp_add_jobserver
@job_name=N'Scrutation espace disque',
@server_name = N'ServerSQL[\instance]';
EXEC msdb.dbo.sp_add_jobstep
@job_name=N'Scrutation espace disque',
@step_name=N'Rapporte l''état du volume du disque',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'EXEC S_SYS.P_AUDIT_SPACE_DISK;',
@database_name=N'msdb',
@flags=0;
EXEC msdb.dbo.sp_update_job
@job_name=N'Scrutation espace disque',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'Data Collector',
@owner_login_name=N'ServerSQL[\instance]',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N'';
EXEC msdb.dbo.sp_add_jobschedule
@job_name=N'Scrutation espace disque',
@name=N'Planification espace disque',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20090312,
@active_end_date=99991231,
@active_start_time=50000,
@active_end_time=235959;
GO |
Partager