1
2
3 /*
4 ** Generated by spgenmsgs.pl on Tue Dec 2 17:55:42 2003
5 **
6 ** raiserror Messages for monitor_server [Total 0]
7 **
8 ** sp_getmessage Messages for monitor_server [Total 0]
9 **
10 ** End spgenmsgs.pl output.
11 **
12 */
13
14 /*
15 ** This stored procedure is a subordinate stored procedure that is invoked from the
16 ** parent stored procedure sp_monitor when "server" is passed as the
17 ** argument or when no argument is passed.
18 ** This stored procedure provides monitoring information pertaining
19 ** to the server as a whole.
20 **
21 ** Parameters:
22 ** None.
23 **
24 ** Returns:
25 ** 0 - upon sucessful completion
26 ** 1 - If an error resulted.
27 */
28
29 create procedure sp_monitor_server
30 as
31
32 /*
33 ** Declare variables to be used to hold current monitor values.
34 */
35 declare @now datetime
36 declare @cpu_busy int
37 declare @io_busy int
38 declare @idle int
39 declare @pack_received int
40 declare @pack_sent int
41 declare @pack_errors int
42 declare @connections int
43 declare @total_read int
44 declare @total_write int
45 declare @total_errors int
46 declare @engonline int
47 declare @interval int
48 declare @mspertick float /* milliseconds per tick */
49 declare @rptline char(255)
50 declare @procval int
51 declare @whoami varchar(30)
52 declare @rtnstatus int
53 declare @monprocname varchar(255)
54
55 select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
56
57 select @monprocname = 'sybsystemprocs.dbo.sp_monitor_check_permission'
58
59 exec @rtnstatus = @monprocname @whoami
60
61 if (@rtnstatus = 1)
62 return (1)
63
64 /*
65 ** Set @mspertick. This is just used to make the numbers easier to handle
66 ** and avoid overflow.
67 */
68
69 select @mspertick = (@@timeticks / 1000.0)
70 /*
71 ** Set engonline to number of engines currently on line (so busy/idle
72 ** figures are correct). If this changes under us, the figures will not
73 ** necessarily be accurate for the next sp_monitor call; this should not
74 ** be a tremendous problem.
75 */
76
77 select @engonline = count(*) from master.dbo.sysengines
78
79
80 /*
81 ** Get current monitor values.
82 */
83 select
84 @now = getdate(),
85 @cpu_busy = @@cpu_busy,
86 @io_busy = @@io_busy,
87 @idle = @@idle,
88 @pack_received = @@pack_received,
89 @pack_sent = @@pack_sent,
90 @connections = @@connections,
91 @pack_errors = @@packet_errors,
92 @total_read = @@total_read,
93 @total_write = @@total_write,
94 @total_errors = @@total_errors
95
96 /*
97 ** Check to see if DataServer has been rebooted. If it has then the
98 ** value of @@boottime will be more than the value of spt_monitor.lastrun.
99 ** If it has update spt_monitor.
100 */
101 update master.dbo.spt_monitor
102 set
103 lastrun = @now,
104 cpu_busy = @cpu_busy,
105 io_busy = @io_busy,
106 idle = @idle,
107 pack_received = @pack_received,
108 pack_sent = @pack_sent,
109 connections = @connections,
110 pack_errors = @pack_errors,
111 total_read = @total_read,
112 total_write = @total_write,
113 total_errors = @total_errors
114 where datediff(ss, lastrun, @@boottime) > 0
115
116 /*
117 ** Now print out old and new monitor values.
118 */
119 set nocount on
120 select @interval = datediff(ss, lastrun, @now)
121 from master.dbo.spt_monitor
122 /* To prevent a divide by zero error when run for the first
123 ** time after boot up
124 */
125 if @interval = 0
126 select @interval = 1
127 select last_run = lastrun, current_run = @now, seconds = @interval
128 from master.dbo.spt_monitor
129 select
130 cpu_busy = convert(char(25),
131 convert(varchar(11),
132 convert(int,
133 (@cpu_busy * (@mspertick / 1000))))
134 + "("
135 + convert(varchar(11),
136 convert(int,
137 ((@cpu_busy - cpu_busy) * (@mspertick / 1000))
138 / @engonline))
139 + ")"
140 + "-"
141 + convert(varchar(11),
142 convert(int,
143 (((@cpu_busy - cpu_busy)
144 * (@mspertick / 1000))
145 / @engonline)) * 100
146 / @interval)
147 + "%"),
148
149 io_busy = convert(char(25),
150 convert(varchar(11),
151 convert(int,
152 (@io_busy * (@mspertick / 1000))
153 / @engonline))
154 + "("
155 + convert(varchar(11),
156 convert(int,
157 ((@io_busy - io_busy) * (@mspertick / 1000))
158 / @engonline))
159 + ")"
160 + "-"
161 + convert(varchar(11),
162 convert(int,
163 (((@io_busy - io_busy) * (@mspertick / 1000))
164 / @engonline))
165 * 100
166 / @interval)
167 + "%"),
168 idle = convert(char(25),
169 convert(varchar(11),
170 convert(int,
171 (@idle * (@mspertick / 1000))
172 / @engonline))
173 + "("
174 + convert(varchar(11),
175 convert(int,
176 ((@idle - idle) * (@mspertick / 1000))
177 / @engonline))
178 + ")"
179 + "-"
180 + convert(varchar(11),
181 convert(int,
182 (((@idle - idle) * (@mspertick / 1000))
183 / @engonline))
184 * 100
185 / @interval)
186 + "%")
187
188 from master.dbo.spt_monitor
189
190 select
191 packets_received = convert(char(25), convert(varchar(11), @pack_received) + "(" +
192 convert(varchar(11), @pack_received - pack_received) + ")"),
193 packets_sent = convert(char(25), convert(varchar(11), @pack_sent) + "(" +
194 convert(varchar(11), @pack_sent - pack_sent) + ")"),
195 packet_errors = convert(char(25), convert(varchar(11), @pack_errors) + "(" +
196 convert(varchar(11), @pack_errors - pack_errors) + ")")
197 from master.dbo.spt_monitor
198
199 select
200 total_read = convert(char(19), convert(varchar(11), @total_read) + "(" +
201 convert(varchar(11), @total_read - total_read) + ")"),
202 total_write = convert(char(19), convert(varchar(11), @total_write) + "(" +
203 convert(varchar(11), @total_write - total_write) + ")"),
204 total_errors = convert(char(19), convert(varchar(11), @total_errors) + "(" +
205 convert(varchar(11), @total_errors - total_errors) + ")"),
206 connections = convert(char(18), convert(varchar(11), @connections) + "(" +
207 convert(varchar(11), @connections - connections) + ")")
208 from master.dbo.spt_monitor
209
210 /*
211 ** Now update spt_monitor
212 */
213 update master.dbo.spt_monitor
214 set
215 lastrun = @now,
216 cpu_busy = @cpu_busy,
217 io_busy = @io_busy,
218 idle = @idle,
219 pack_received = @pack_received,
220 pack_sent = @pack_sent,
221 connections = @connections,
222 pack_errors = @pack_errors,
223 total_read = @total_read,
224 total_write = @total_write,
225 total_errors = @total_errors
226
227 return (0)
228
exec sp_procxmode 'sp_monitor_server', 'AnyMode'
go
Grant Execute on sp_monitor_server to public
go
