· 8 years ago · Dec 14, 2017, 01:58 PM
1/*
2 ******************
3 MagicXPI Log Archive
4 ******************
5
6 Author: Ryan Portelli
7 Created: 14-Dec-2017
8
9 This script creates a new table in the MagicXPI database [MGXPI4_1] as [CSOL_LogArchive],
10 then creates a SQL trigger on the main log table [ifs_actlog] so that if the number of rows following an INSERT exceeds
11 100000 amount, 100000 rows will be moved into the archive table.
12
13
14 ***** Change log *****
15 14-Dec-2017 - v1.0 - First Edition
16
17
18
19*/
20-- Set database for the creation of all the below scripts to the MagicXPI DB, regardless of which DB the connection is currently looking at
21USE [MGXPI4_1]
22GO
23
24SET ANSI_NULLS ON
25GO
26
27SET QUOTED_IDENTIFIER ON
28GO
29
30SET ANSI_PADDING ON
31GO
32
33--If our archive table doesnt already exist...
34IF EXISTS (
35 SELECT *
36 FROM [?].INFORMATION_SCHEMA.TABLES
37 WHERE TABLE_NAME = 'tbl_CSOL_LogArchive'
38 )
39BEGIN
40 PRINT 'Table [tbl_CSOL_LogArchive] already exists. Skipping this step...'
41END
42ELSE
43BEGIN
44 --...create the archive table and associated indexes exactly like the main log table
45 CREATE TABLE [dbo].[tbl_CSOL_LogArchive] (
46 [SERVERID] [float] NOT NULL DEFAULT((0))
47 , [MSGID] [float] NOT NULL DEFAULT((0))
48 , [BPID] [float] NOT NULL DEFAULT((0))
49 , [FLOWID] [float] NOT NULL DEFAULT((0))
50 , [FSID] [float] NOT NULL DEFAULT((0))
51 , [FSSTEP] [float] NOT NULL DEFAULT((0))
52 , [MESSAGETYPEID] [float] NOT NULL DEFAULT((0))
53 , [MESSAGESTRING] [nvarchar](1000) NOT NULL DEFAULT(' ')
54 , [USERBLOB] [image] NULL
55 , [USERCODE] [float] NOT NULL DEFAULT((0))
56 , [CREATEDATE] [datetime] NOT NULL DEFAULT(getdate())
57 , [CREATETIME] [char](9) NOT NULL DEFAULT(' ')
58 , [OBJECTLEVEL] [float] NOT NULL DEFAULT((0))
59 , [CATEGORY] [nvarchar](30) NOT NULL DEFAULT(' ')
60 , [USERKEY1] [nvarchar](30) NOT NULL DEFAULT(' ')
61 , [USERKEY2] [nvarchar](30) NOT NULL DEFAULT(' ')
62 , [VERSIONKEY] [varchar](30) NOT NULL DEFAULT(' ')
63 , [STATUSCODE] [float] NOT NULL DEFAULT((0))
64 , [SEVERITY] [float] NOT NULL DEFAULT((0))
65 , [EXTENSION] [varchar](10) NOT NULL DEFAULT('')
66 , [PROJECTKEY] [varchar](30) NOT NULL DEFAULT(' ')
67 , [BLOBEXISTS] [smallint] NOT NULL DEFAULT((0))
68 , [ROOTFSID] [float] NOT NULL DEFAULT((0))
69 , [FLOWREQUESTID] [float] NOT NULL DEFAULT((0))
70 , [FILELOCATION] [varchar](3000) NOT NULL DEFAULT(' ')
71 , [RUNID] [varchar](36) NULL
72 , PRIMARY KEY CLUSTERED ([MSGID] ASC) WITH (
73 PAD_INDEX = OFF
74 , STATISTICS_NORECOMPUTE = OFF
75 , IGNORE_DUP_KEY = OFF
76 , ALLOW_ROW_LOCKS = ON
77 , ALLOW_PAGE_LOCKS = ON
78 ) ON [PRIMARY]
79 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
80
81 CREATE UNIQUE NONCLUSTERED INDEX [csol_archive_actlogkey01] ON [dbo].[CSOL_LogArchive] (
82 [PROJECTKEY] ASC
83 , [SERVERID] ASC
84 , [MSGID] ASC
85 )
86 WITH (
87 PAD_INDEX = OFF
88 , STATISTICS_NORECOMPUTE = OFF
89 , SORT_IN_TEMPDB = OFF
90 , IGNORE_DUP_KEY = OFF
91 , DROP_EXISTING = OFF
92 , ONLINBE = OFF
93 , ALLOW_ROW_LOCKS = ON
94 , ALLOW_PAGE_LOCKS = ON
95 )
96END
97
98-- Create StoredProc to move rows from log table to Archive table
99CREATE PROCEDURE [dbo].[sp_CSOL_ClearLogs] @MSGID FLOAT NULL
100AS
101BEGIN
102 /*
103 ******************
104 sp_CSOL_ClearLogs
105 ******************
106
107 Author: Ryan Portelli
108 Created: 14-Dec-2017
109
110 This stored procedure deletes all of the rows in the MagicXPI log table [ifs_ActLog] that are older than the provided message ID (MSGID)
111 If no messageID is provided, it instead deletes all rows in [ifs_Actlog] which already exist in [tbl_CSOL_LogArchive]
112
113 ***** Change log *****
114 14-Dec-2017 - v1.0 - First Edition
115
116 */
117 SET NOCOUNT ON;
118
119 IF @MSGID IS NULL
120 BEGIN
121 --If a specific MSGID isn't provided, just clear log files that already exist in [tbl_CSOL_LogArchive]
122 --Check Archive table exists, and fail with an error if it does not
123 IF NOT EXISTS (
124 SELECT *
125 FROM [?].INFORMATION_SCHEMA.TABLES
126 WHERE TABLE_NAME = 'tbl_CSOL_LogArchive'
127 )
128 BEGIN
129 RAISERROR (
130 'Table [tbl_CSOL_LogArchive] does not exist. Please ensure it is created.'
131 , 20
132 , - 1
133 );
134 END
135
136 --Initialise records to tempDB to delete to reduce number of DB reads
137 SELECT MSGID
138 INTO #MSGTEMP
139 FROM [dbo].[ifs_ActLog]
140 WHERE MSGID IN (
141 SELECT MSGID
142 FROM [dbo].[tbl_CSOL_LogArchive]
143 );
144
145 -- While there are records in tempDB...
146 WHILE (
147 SELECT COUNT(*)
148 FROM #MSGTEMP
149 ) > 0
150 BEGIN
151 --...delete top 500 matches
152 DELETE
153 FROM [dbo].[ifs_actlog]
154 WHERE MSGID IN (
155 SELECT TOP 500 MSGID
156 FROM #MSGTEMP
157 ORDER BY MSGID
158 );
159
160 DELETE
161 FROM #MSGTEMP
162 WHERE MSGID IN (
163 SELECT TOP 500 MSGID
164 FROM #MSGTEMP
165 ORDER BY MSGID
166 );
167 END
168 END
169 ELSE
170 BEGIN
171 --if a MSGID is provided, delete all records equal to and less than it
172 --Initialise records to tempDB to delete to reduce number of DB reads
173 SELECT MSGID
174 INTO #MSGTEMP
175 FROM [dbo].[ifs_ActLog]
176 WHERE MSGID <= (
177 SELECT MAX(MSGID)
178 FROM [dbo].[ifs_actlog]
179 WHERE CREATEDATE = @CREATEDATE
180 AND CREATETIME = @CREATETIME
181 );
182
183 -- While there are records in tempDB...
184 WHILE (
185 SELECT COUNT(*)
186 FROM #MSGTEMP
187 ) > 0
188 BEGIN
189 --...delete top 500 matches
190 DELETE
191 FROM [dbo].[ifs_actlog]
192 WHERE MSGID IN (
193 SELECT TOP 500 MSGID
194 FROM #MSGTEMP
195 ORDER BY MSGID
196 );
197
198 DELETE
199 FROM #MSGTEMP
200 WHERE MSGID IN (
201 SELECT TOP 500 MSGID
202 FROM #MSGTEMP
203 ORDER BY MSGID
204 );
205 END
206 END
207
208 --Drop the temp table if it exists
209 IF OBJECT_ID('tempdb..#MSGTEMP') IS NOT NULL
210 BEGIN
211 DROP TABLE #MSGTEMP
212 END
213END
214GO
215
216-- Create StoredProc to move rows from log table to Archive table
217CREATE PROCEDURE [dbo].[sp_CSOL_ArchiveLogs]
218AS
219BEGIN
220 /*
221 ******************
222 sp_CSOL_ArchiveLogs
223 ******************
224
225 Author: Ryan Portelli
226 Created: 14-Dec-2017
227
228 This stored procedure copies all of the rows in the MagicXPI log table [ifs_actlog] into
229 an Archive table [tbl_CSOL_LogArchive], then runs the [sp_CSOL_ClearLogs] stored procedure to clear the log table.
230
231 ***** Change log *****
232 14-Dec-2017 - v1.0 - First Edition
233
234
235
236 */
237 SET NOCOUNT ON;
238
239 --Check Archive table exists, and fail with an error if it does not
240 IF NOT EXISTS (
241 SELECT *
242 FROM [?].INFORMATION_SCHEMA.TABLES
243 WHERE TABLE_NAME = 'tbl_CSOL_LogArchive'
244 )
245 BEGIN
246 RAISERROR (
247 'Table [tbl_CSOL_LogArchive] does not exist. Please ensure it is created.'
248 , 20
249 , - 1
250 )
251 END
252 ELSE
253 BEGIN
254 --Fill the archive table with everything that is currently in the log table
255 INSERT INTO [dbo].[tbl_CSOL_LogArchive] (
256 SERVERID
257 , MSGID
258 , BPID
259 , FLOWID
260 , FSID
261 , FSSTEP
262 , MESSAGETYPEID
263 , MESSAGESTRING
264 , USERBLOB
265 , USERCODE
266 , CREATEDATE
267 , CREATETIME
268 , OBJECTLEVEL
269 , CATEGORY
270 , USERKEY1
271 , USERKEY2
272 , VERSIONKEY
273 , STATUSCODE
274 , SEVERITY
275 , EXTENSION
276 , PROJECTKEY
277 , BLOBEXISTS
278 , ROOTFSID
279 , FLOWREQUESTID
280 , FILELOCATION
281 , RUNID
282 )
283 SELECT SERVERID
284 , MSGID
285 , BPID
286 , FLOWID
287 , FSID
288 , FSSTEP
289 , MESSAGETYPEID
290 , MESSAGESTRING
291 , USERBLOB
292 , USERCODE
293 , CREATEDATE
294 , CREATETIME
295 , OBJECTLEVEL
296 , CATEGORY
297 , USERKEY1
298 , USERKEY2
299 , VERSIONKEY
300 , STATUSCODE
301 , SEVERITY
302 , EXTENSION
303 , PROJECTKEY
304 , BLOBEXISTS
305 , ROOTFSID
306 , FLOWREQUESTID
307 , FILELOCATION
308 , RUNID
309 FROM [dbo].[ifs_actlog];
310
311 --execute the cleanup stored proc, if it exists
312 IF EXISTS (
313 SELECT *
314 FROM sys.objects
315 WHERE object_id = OBJECT_ID(N'sp_CSOL_ClearLogs'
316 AND type IN (N'P', N'PC'))
317 )
318 BEGIN
319 EXEC [dbo].[sp_CSOL_ClearLogs];
320 END
321 ELSE
322 BEGIN
323 PRINT 'Cleanup stored procedure sp_CSOL_ClearLogs does not exist. Log rows have been entered into the archive table, but not removed from the log table.'
324 END
325 END
326END
327GO
328
329--Create SQL Trigger to automatically clear the logs if they reach 100000 rows
330CREATE TRIGGER tgr_CSOL_ifs_actlog ON [dbo].[ifs_ActLog]
331AFTER INSERT
332AS
333BEGIN
334 /*
335 ******************
336 tgr_CSOL_ifs_actlog
337 ******************
338
339 Author: Ryan Portelli
340 Created: 14-Dec-2017
341
342 This SQL Trigger on the MagicXPI log table [ifs_actlog] checks to see if the rowcount of the
343 table exceeds 100000 rows, and if so launches the archive stored procedure [sp_CSOL_ArchiveLogs]
344
345 ***** Change log *****
346 14-Dec-2017 - v1.0 - First Edition
347
348 */
349 SET NOCOUNT ON;
350 --Turn off transact-abort so we don't stop any transactions if our code fails
351 --and use TRY/CATCH to handle any errors
352 SET XACT_ABORT OFF;
353
354 BEGIN TRY
355 --store the current number of records in the log table
356 DECLARE @ROWCOUNT BIGINT
357
358 SELECT @ROWCOUNT = COUNT(MSGID)
359 FROM [dbo].[ifs_ActLog]
360
361 --if the number of records in the log table exceeds the specified amount...
362 IF @ROWCOUNT >= 100000
363 BEGIN
364 --...and the stored procedure does exist...
365 IF EXISTS (
366 SELECT *
367 FROM sys.objects
368 WHERE object_id = OBJECT_ID(N'sp_CSOL_ArchiveLogs'
369 AND type IN (N'P', N'PC'))
370 )
371 BEGIN
372 --start the archiving process
373 EXEC [dbo].[sp_CSOL_ArchiveLogs];
374 END
375 END
376 END TRY
377
378 BEGIN CATCH
379 PRINT 'SQL Trigger tgr_CSOL_ifs_actlog encountered an error: ' + error_message()
380 END CATCH;
381END