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 |
FAQ taux d'occupation des disques
Répondre avec citation
Parce que je suis bien bloqué là
Partager