1
2 create procedure sp_monitor_archive(
3 @montype varchar(30) = NULL
4 , @archivecmd varchar(255) = NULL
5 , @trace int = NULL
6 ) as
7 begin
8 declare @whoami varchar(30)
9 , @indent varchar(32)
10 , @retval int
11 , @mda_retval int
12 , @found_montype tinyint
13
14 , @opt_sep char(1)
15 , @charindex tinyint
16 , @archive_str varchar(7)
17 , @option_item varchar(30)
18 , @datestamp datetime
19
20 , @archive_syntax varchar(40)
21
22 -- We restrict this to a short string for 125.x due to the
23 -- limited tablename length.
24 --
25 , @arch_prefix_dflt varchar(8)
26 , @arch_prefix_maxlen int
27 , @arch_prefix varchar(30)
28
29 , @arch_dbname varchar(30)
30 , @baseprocname varchar(100)
31 , @archprocname varchar(60)
32
33 -- DB status flags
34 , @sel_into_ok int -- status word
35 , @user_proxy_db int
36 , @ha_proxy_db int
37
38 -- Variables to store status bit settings.
39 , @sel_into_flag int
40 , @proxy_db_flag int
41
42 select @whoami = object_name(@@procid, db_id('sybsystemprocs'))
43 , @indent = char(10) + space(2 * @@nestlevel)
44 , @archive_str = 'archive'
45
46 -- This will be the single timestamp used for all data archived
47 -- under this command. This way, this set of data can be all
48 -- joined in one pass, using this common field.
49 --
50 , @datestamp = getdate()
51
52 -- Various status flags for database settings.
53 , @sel_into_ok = 4
54 , @user_proxy_db = 1
55 , @ha_proxy_db = 2
56
57 -- Assume error till we know that it's a valid monitoring type
58 , @found_montype = 0
59
60 if (@montype IN ('deadlock'
61 ))
62 begin
63 select @found_montype = 1
64 end
65
66 -- This means user asked to archive data for a monitoring type that is
67 -- an illegal monitoring type, or archiving for that type is currently
68 -- not supported. Raise an error and bail.
69 --
70 if (@found_montype = 0)
71 begin
72 select @archive_syntax = "sp_monitor 'help', '"
73 + @archive_str
74 + "'"
75 raiserror 19260, @archive_syntax
76 return 1
77 end
78
79 exec @retval = sp_monitor_check_permission @whoami
80 if (@retval != 0)
81 return @retval
82
83 -- Do routine monitoring specific validation for setup/configuration.
84 -- 'archive' is not really a monitoring type, so instead pass-in a
85 -- dummy sproc name as the arg, which will be anyway ignored .
86 --
87 exec @retval = sp_monitor_verify_setup 'sp_monitor_archive'
88
89 exec @mda_retval = sp_monitor_verify_cfgval @montype
90
91 -- Bail out if there were any errors.
92 if (@retval != 0)
93 return @retval
94 else if (@mda_retval != 0)
95 return @mda_retval
96
97 if (@trace IS NULL)
98 exec @trace = sp_monitor_trace_level
99
100 if (@trace = 1)
101 begin
102 print "%1!---- Start Trace %2! montype='%3!' archivecmd='%4!' datestamp='%5!'"
103 , @indent, @whoami
104 , @montype
105 , @archivecmd
106 , @datestamp
107 end
108
109 if (@montype IS NULL)
110 begin
111 raiserror 18464, "'@monitoring_type'"
112 return 1
113 end
114 else if (@archivecmd IS NULL)
115 begin
116 raiserror 18464, "'@archivecmd'"
117 return 1
118 end
119
120 -- Always archive in the current db of the sproc's execution.
121 select @arch_dbname = db_name()
122
123 -- Disallow certain key system-dbs for archival uses.
124 if (@arch_dbname IN ('master'
125 , 'model'
126 , 'sybsystemprocs'
127 , 'sybsystemdb'
128 , 'sybsecurity'
129 )
130 )
131 begin
132 raiserror 19492, @arch_dbname, "system"
133 return 1
134 end
135
136 -- Check if archive db allows for select-into and return error
137 -- now, to avoid future errors at run-time.
138 --
139 select @sel_into_flag = (d.status & @sel_into_ok)
140 from master.dbo.sysdatabases d
141 where name = @arch_dbname
142
143 if (@sel_into_flag != @sel_into_ok)
144 begin
145 exec sp_getmessage 19261, @baseprocname output
146 print @baseprocname, "sp_monitor", @archive_str,
147 "sp_dboption", "select into"
148
149 -- Reuse @var to get string name for 17050.
150 exec sp_getmessage 17050, @baseprocname output
151 raiserror 19493, @arch_dbname, @baseprocname
152 return 1
153 end
154
155 -- check and disallow user- and HA-proxy dbs as an archive db.
156 --
157 select @proxy_db_flag = (d.status3 & (@user_proxy_db | @ha_proxy_db))
158 from master.dbo.sysdatabases d
159 where name = @arch_dbname
160
161 if (@proxy_db_flag IN (@user_proxy_db, @ha_proxy_db))
162 begin
163 raiserror 19492, @arch_dbname, "proxy"
164 return 1
165 end
166
167 select @archive_syntax = "'" + @archive_str + " [using prefix=<string>]'"
168 exec @retval = sp_monitor_parse_archive_cmd
169 @archivecmd
170 , @archive_str
171 , @archive_syntax
172 , @arch_prefix output
173 , @trace
174 if (@retval != 0)
175 return @retval
176
177 -- =================================================================
178 -- Call the monitoring-type specific archival routine
179 -- Currently, we only support archiving for one monitoring type.
180 -- In future, we might consider extending this to archive for 'all'
181 -- monitoring types in one go.
182 --
183 -- Build the procedure's name prefixed by the archive db name, so that
184 -- when we execute the specific archiving sproc, it will run in the
185 -- archive db. (We just have to append the monitoring type for each
186 -- type of monitoring whose data will be archived.)
187 --
188 select @baseprocname = @arch_dbname + ".." + "sp_mon_archive_"
189
190 if (@montype IN ('deadlock', 'all'))
191 begin
192 select @archprocname = @baseprocname + 'deadlock'
193 , @found_montype = 1
194
195 -- The reason why we getdate() here and pass it all the way
196 -- down the stack is that sometime in the future when we
197 -- support archival for other monitoring types (or 'all')
198 -- we would like to keep the archive date across multiple tables
199 -- the same for all data that is being archived in one command.
200 --
201 exec @retval = @archprocname @arch_prefix
202 , @datestamp
203 , @trace
204 if (@retval != 0)
205 return @retval
206 end
207
208
209 return @retval
210 end -- }
211
