Event deletion job does not work

Bharath Kumar shared this problem 6 years ago
Resolved

Hi,

We have set the job on event and event archive table following the below:

INSERT INTO Configuration VALUES (1, ‘SYSTEM’, ‘EVENTMAXDAYS’, 1)

INSERT INTO Configuration VALUES (1, ‘SYSTEM’, ‘EVENTARCHIVEMAXDAYS’, 1)

We have set this job for all the client orgs in our database. But I see this job is not working and is not cleaning up the old records in the event table.

When I see the task schedule for the status of the event archive job, it says status failed:

FAILURE;com.microsoft.sqlserver.jdbc.SQLServerException: socket closed;0;NULL;NULL;NULL

Can you please provide some solution how to configure to make this job working.

Regards,

Bharath

Replies (43)

photo
1

Hi Bharath,

Because of the "socket closed" exception I'm wondering whether the connection to the SmartReporting configuration database is timing out. So going along this line of thought could you please add the JDBCTimeout parameter to your web.xml file and give it a high value (e.g. 1 hour) as described in the following Knowledge Base article:

https://community.yellowfinbi.com/knowledge-base/article/how-to-increase-the-connection-timeout-to-the-yellowfin-database

(and then don't forget to restart SmartReporting)

Please let me know whether this helps or not.

regards,

David

photo
1

Hi Dave,

Thanks for the response. We already have the jdbc timeout as 3600.

Regards,

Bharath

photo
1

Hi Bharath,

OK, that should easily be enough time.

I decided to test this out on a BMC build of Yellowfin 7.3 and it worked for me (unfortunately! I would have been very happy if I got an Exception like you)

See below, you can see that the EventArchiveTask last ran at '20180510022600' (that is in GMT) and the system time was 12:28 (in GMT that's '20180510022800'),

thus it had just run 2 minutes prior to the screenshot, and you can see that the LastRunStatus was "SUCCESS"

/AbP26WwVPWeDAAAAAElFTkSuQmCCAA==

and then I checked the Event table and it only contained today's events, which is the expected result.

Anyway, that's where my investigation is at the moment.

Could you please tell me exactly which build of 7.3 you are using?

And also please send across the SmartReporting.log file (the application log)

thanks,

David

photo
1

Hello again Bharath,

actually, just to play it safe, could you please send across all the logs from the logs folder.

thanks,

David

photo
1

Sure Dave, I will get the logs.

photo
1

great, thanks!

photo
1

Hi Dave,

Attached the info.jsp file and the logs.

Thanks in advance!

Regards,

Bharath

photo
1

Hi Bharath,

thanks for the logs, there are lots of errors in them, and so to help me pinpoint which are relevant to the issue at hand could you please tell me whether you manually ran the Event deletion job, and if so, what time did you run it?

Otherwise, if you didn't manually run it, then please tell me what time it is configured to regularly run. You can find out this information by running the following query:


SELECT * 
FROM TaskSchedule 
WHERE ScheduleUnitCode = 'EventArchiveTask'
thanks,

David

photo
1

Hi Dave,


Can you please check the attachment of the whole TaskSchedule output in excel.

We tried to run manual deletion once a day, when I came to work around 8 AM.

Regards,

Bharath

photo
1

Hi Bharath,

thanks for the information, I can see that your EventArchiveTask is configured to run daily at 15:34:48 which is GMT, so when converted to your timezone (GMT - 7) that becomes 8:34 AM.

But unfortunately when I look through the smartreporting.log files from 8:21 AM (because that's what time the oldest log file starts, "smartreporting.log.9") up to 9 AM

the only error I can see is:

Error occurred when connecting to the database: java.lang.Exception: Source AR System is marked as unavailable

which is nothing to do with the Event Deletion Job issue, as you would know, SmartReporting does not need to connect to the Source AR System to move and delete records in the SmartReporting configuration database, instead it needs to connect to the ARReport database.


Also, the log file called jdbc.log is the specific log file for connections to the Smart Reporting configuration database and it shows no errors at all around the time of 8 AM:

2018-05-14 05:41:59   INFO: Connection[10] successfully reopened
2018-05-14 08:11:00   INFO: Created Connection[12]
2018-05-14 09:17:00   INFO: Connection[0] has been open for 4 h, 521 ms and will be recycled
This means that unfortunately there was nothing to help my investigation in that set of logs.


However, just out of interest I looked at the source code for the class which is responsible for deleting the Event Archive records and I saw in the constructor the following lines:

   public EventArchiveTask(TaskScheduleBean tsb) {
      log.debug("Entering EventArchiveTask");
and then further on in the class were also the following lines:

         log.debug("Max days in Event table: " + eventMaxDays);
         log.debug("Max days in EventArchive table: " + eventArchiveMaxDays);
so this definitely means that if you change your logging level to DEBUG just for the EventArchiveTask class during the period when the EventArchiveTask runs then we should at least see the first of the above entries in the debug log file.

The way to do this is to add the following line to your log4j.properties file (<smartreporting>\appserver\webapps\ROOT\WEB-INF):


log4j.category.com.hof.servlet.EventArchiveTask=DEBUG


I have just tested this out over here and it worked fine for me, here are the entries in the yellowfin.log after the EventArchiveTask ran:


YF:2018-05-16 16:27:00: INFO (LicenceCheckProcess:checkDataSource) - DataSources: 1
YF:2018-05-16 16:27:00: INFO (LicenceCheckProcess:checkDataSource) - Primary Org DataSources: 1
YF:2018-05-16 16:27:00: INFO (LicenceCheckProcess:checkDataSource) - Client Org DataSources: 0
YF:2018-05-16 16:27:00: INFO (MIDataSourceEditAction:execute) - MIDataSourceEditAction exiting with action: MIAdminConsole
YF:2018-05-16 16:27:00: INFO (SystemTaskManager:run) - Instantiating class: EventArchiveTask
YF:2018-05-16 16:27:00: INFO (MIPreAdminConsoleAction:execute) - MIPreAdminConsoleAction entered
YF:2018-05-16 16:27:00:DEBUG (EventArchiveTask:<init>) - Entering EventArchiveTask
YF:2018-05-16 16:27:00:DEBUG (EventArchiveTask:<init>) - Max days in Event table: 1
YF:2018-05-16 16:27:00:DEBUG (EventArchiveTask:<init>) - Max days in EventArchive table: 1
YF:2018-05-16 16:27:00: INFO (MIPreAdminConsoleAction:execute) - MIPreAdminConsoleAction exiting
YF:2018-05-16 16:27:01: INFO (ColourPickerAjaxAction:runAction) - ColourPickerAjaxAction entered
YF:2018-05-16 16:27:01: INFO (MIAdminConsoleAjaxAction:runAction) - httpMethod -> GET
YF:2018-05-16 16:27:13: INFO (SystemTaskManager:run) - Success instantiating class: EventArchiveTask
YF:2018-05-16 16:27:13: INFO (SystemTaskManager:run) - Instantiating class: GroupFlattenTask
YF:2018-05-16 16:27:13: INFO (SystemTaskManager:run) - Success instantiating class: GroupFlattenTask


Incidentally, instead of waiting for 8 AM each day for the task to run, you can reconfigure the task to run every minute with the following query:


UPDATE TaskSchedule

SET FrequencyTypeCode = 'MINUTES',

FrequencyUnit = 1

WHERE ScheduleUnitCode = 'EventArchiveTask'


Having said that, I found that YF is checking the task table every 5 minutes, that means that shortest interval you can get the task to run is every 5 minutes (not every 1 minute)


Sorry this email is so long, but this investigation is proving difficult, hopefully your new debug logging for the EventArchiveTask class will give us something to go on.


regards,

David

photo
1

Thanks a Lot David. I will get back to you with the event archive debug logs.

Regards,

Bharath

photo
1

great! I await the debug logs...

photo
1

Hi Dave,

I’ve changed the database with command:

UPDATE TaskSchedule

SET FrequencyTypeCode = 'MINUTES',

FrequencyUnit = 1

WHERE ScheduleUnitCode = 'EventArchiveTask'

And I can see in the smartreporting.log Line 102:

BMC:SR:2018-05-18 09:47:48: INFO (LogonAction:execute) - Logon Action entered

BMC:SR:2018-05-18 09:47:48: INFO (LogonAction:performLogin) - Internal Entry Point for Logon

BMC:SR:2018-05-18 09:47:48: INFO (LogonAction:performLogin) - logon authorised..

BMC:SR:2018-05-18 09:49:00:DEBUG (EventArchiveTask:<init>) - Entering EventArchiveTask

BMC:SR:2018-05-18 09:49:00:DEBUG (EventArchiveTask:<init>) - Max days in Event table: 1

BMC:SR:2018-05-18 09:49:00:DEBUG (EventArchiveTask:<init>) - Max days in EventArchive table: 1

I’ve also attached the logs folder from today, after the database update and restarting for Smart Reporting servie.

Let me know if it helps.

Regards,

Bharath

photo
1

Hi Bharath,

thanks for doing that, I've looked through all the logs and the strange thing is that I can't see anything in the logs that would indicate something has gone wrong with the EventArchiveTask.

As you pointed out, there there are no errors in the smartreporting debug log, and there are also no errors around the same time (2018-05-18 09:49:00) in the JDBC log which is the log file that records information about connections to the ARReport database:

2018-05-18 09:46:10   INFO: Created Connection[7]
2018-05-18 09:47:00   INFO: Created Connection[8]
2018-05-18 09:51:00   INFO: Created Connection[9]
2018-05-18 09:55:00   INFO: Created Connection[10]
2018-05-18 10:03:00   INFO: Created Connection[11]
2018-05-18 10:04:00   INFO: Created Connection[12]
However there is still one more thing we can look, sorry I didn't notice it earlier but I just noticed now that SmartReporting creates an entry in the Event table when the EventArchive job runs, I just ran mine now and here is the EventData column from the relevant Event record:


EventMaxDays=1,EventArchiveMaxDays=30,EventsArchived=58,ArchivedEventsDeleted=17,Duration=25ms
So could you please run the following query and send across the results:


select * from event where eventcode = 'EVENTARCHIVE'

Also, I'm still wondering the problem is actually that SQL Server is closing the connection itself and therefore your DBA needs to re-configure the database to stop this situation.

Is there anything in SQL Server's audit logs abou the "Socket Closed" error?


regards,

David

photo
1

Hi David,

Here is the result of select*fromeventwhere eventcode ='EVENTARCHIVE'

I’ve talked to dba, and they haven’t found any socket closed error in any log for the past 7 days.

Regards,

Bharath

photo
1

Hi Bharath,

thanks for the Event data, and the most recent entry in it is from "2018-05-22 10:59:00" (GMT) and shows the following:

EventsArchived=163ArchivedEventsDeleted=163Duration=68ms

which means it was successful.

and in fact, every entry before it (I see they are running every 5 minutes) was successful.

This is a very strange state of affairs. So far what we've learned is:


1) In the Schedule Manager the EventArchiveTask shows a FAILURE with the error "com.microsoft.sqlserver.jdbc.SQLServerException: socket closed"

2) In the smartreporting log and also the jdbc log there are no corresponding errors.

3) In the Event table the job is recorded as having worked correctly.

4) your DBA says that there isn't anything on his end that indicates the DBMS closed the socket.


I'm going to have to ask someone cleverer than me about this if you can please bear with me, hopefully I'll be able to get back to you in a day.

regards,

David

photo
1

Hello again Bharath,

actually, before I do that I've just thought of something else.

I noticed in your JDBC log that you have "Verify Connections" turned off. Therefore I think it would be well worth it to turn it on and see if that resolves the issue.

The following Knowledge Base article explains how to do this:

https://community.yellowfinbi.com/knowledge-base/article/jdbc-verify

Please let me know how it goes.

regards,

David

photo
1

Hi Dave,

We did set the jdbc verification to true:


2018-05-28 13:20:47 NOTICE: Verify connections: true


2018-05-28 13:20:47 NOTICE: Application Version: 7.3


And restart the Smart Reporting service after the modification.


In 2 hours, the event table has 4000 records. Any other logs you need?

Regards,

Bharath

photo
1

Hi Bharath,

well thanks for giving that a try, and it's a pity it didn't resolve the issue!

Although, I was just thinking, you set the EVENTMAXDAYS value to 1, so I wouldn't expect it to do anything in 2 hours, you would have to wait until the date has clicked over to the next day.

In other words, to test whether the job is working, you will have to update the LastRunGMTDateTime so that it is showing the day before.

regards,

David

photo
1

Hi David,

Today the number of entries has increased to 38000 records.

Bharath

photo
1

Hi Bharath,

OK, it doesn't happen often to me but I think I am defeated here! I will have to use the "phone a friend" option now (actually, it will be "email").

So I have collected all of the necessary information and emailed the developer who I think is best able to help us for this issue. It may take a day or two to get an answer because he is in the GMT timezone and also he is usually very busy.

Hopefully he'll have something useful to contribute, I'll let you know as soon as he replies.

regards,

David

photo
1

Thanks David! Appreciate your help.

I will wait for your developers update.

Regards,

Bharath

photo
1

Hi Bharath,

they said that it just sounds like a timeout on the Yellowfin side, and that there really should be something in the jdbc log regardless of whether it is Yellowfin or not that is disconnecting it.

So with this in mind could you please turn on DEBUG logging for the JDBC log, hopefully that will bring up something useful.

The way to increase the logging level of the JDBC log file is to find the following line in the log4j.properties file (<yellowfin>\appserver\webapps\ROOT\WEB-INF):

log4j.category.com.hof.pool.DBConnectionPool.jdbclog=INFO,jdbclog
and change the INFO to DEBUG.


We await the results...


regards,

David

photo
1

Hi David,

I’ve set the jdbc log to debug and restarte the smart reporting.

Here is today’s log so far

photo
1

Hi Bharath,

thanks for the DEBUG JDBC logs, although unfortunately there are no errors at all in them.

However, there is the following interesting error in the smartreporting log:

BMC:SR:2018-05-30 21:05:00:DEBUG (EventArchiveTask:<init>) - Entering EventArchiveTask
BMC:SR:2018-05-30 21:05:00:DEBUG (EventArchiveTask:<init>) - Max days in Event table: 1
BMC:SR:2018-05-30 21:05:00:DEBUG (EventArchiveTask:<init>) - Max days in EventArchive table: 1
BMC:SR:2018-05-30 21:05:00:ERROR (DBAction:doSelect) - Error occured selecting data: java.sql.SQLException: Form not found: 'HPD_Help_Desk'. Note that form names are case sensitive and form names with spaces or special characters must be enclosed in backticks (`)
java.sql.SQLException: Form not found: 'HPD_Help_Desk'. Note that form names are case sensitive and form names with spaces or special characters must be enclosed in backticks (`)
	at com.bmc.arsys.jdbc.cache.FormFldsCache.getEntry(FormFldsCache.java:204)
	at com.bmc.arsys.jdbc.framework.querybuilders.ARSelectAPIQueryBuilder.getFieldId(ARSelectAPIQueryBuilder.java:1251)

although that error comes from SmartReporting trying to run a report, not from deleting Event records. But the error does occur in the same second as the EventArchiveTask, so maybe it is causing the ARJDBC driver to stop working and thus indirectly affects the EventArchiveTask.


If we go with this theory for the moment, then a good test to do would be to pause all of the current schedules that are failing and see if that stops the EventArchiveTask from failing.

To do this run the following query:

SELECT rh.ReportName
FROM ReportBroadcast rb
INNER JOIN ReportHeader rh
ON rb.ReportId = rh.ReportId
WHERE BroadcastId IN (17571779, 10582482, 14589300, 16219523, 18940603, 16595050, 17980273, 13097389, 10442561)


and that will give you a result set of all of the report names for the failing tasks.

Then go to the Schedule Manager and locate each of those report names in the "Schedule" column, and then expand the task and then click the Pause button for each of the 9 Scheduled Tasks.


And if we are unlucky and that theory proves to be incorrect, then another thing which I think could be useful would be if you set the Profiler in SQL Server Management Studio running for about 10 minutes to catch the EventArchiveTask running. And then send us the resulting trace file.


I hope that makes sense, please let me know how it goes.


regards,

David

photo
1

Hi Dave,

Thanks for all your help so far. Can you explain this


«And if we are unlucky and that theory proves to be incorrect, then another thing which I think could be useful would be if you set the Profiler in SQL Server Management Studio running for about 10 minutes to catch the EventArchiveTask running. And then send us the resulting trace file.»


Not sure how to do this.

-Bharath

photo
1

Also is it enough to set in on in 10 minute random time of the day or do we have to do this in a specific time?

photo
1

Hi Bharath,

yes that documentation on SQL Server Profile is what I was talking about.

And I suggested 10 minutes because I thought that time interval should be enough to capture the EventArchiveTask because I think you had it running every 5 minutes. The main thing is that the Profiler trace log covers a time period when the EventArchiveTask has attempted to run.

By the way, I went back to your first batch of log files (from 14th May) and noticed in the JDBC log file that there were a lot of occurrences of the following error:

2018-05-14 01:10:45  ERROR: Connection[0] is closed
2018-05-14 01:11:00  ERROR: Failed to reopen connection[0]: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host NSADB038, port 1433 has failed. Error: "Connection timed out: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host NSADB038, port 1433 has failed. Error: "Connection timed out: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
	at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:242)
	at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2369)
	at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:551)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1963)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
	at com.hof.pool.JDBCConnection.openConnection(JDBCConnection.java:179)
	at com.hof.pool.DBConnection.authenticateAndOpenConnection(DBConnection.java:127)
	at com.hof.pool.DBConnectionPool.A(DBConnectionPool.java:1211)
	at com.hof.pool.DBConnectionPool.E(DBConnectionPool.java:1684)
	at com.hof.pool.DBConnectionPool.A(DBConnectionPool.java:232)
	at com.hof.pool.DBConnectionPool$_A.run(DBConnectionPool.java:2002)
	at java.util.TimerThread.mainLoop(Unknown Source)
	at java.util.TimerThread.run(Unknown Source)
2018-05-14 01:11:00   WARN: Execution problem occurred when clearing warnings: java.util.concurrent.ExecutionException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
java.util.concurrent.ExecutionException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
	at java.util.concurrent.FutureTask.report(Unknown Source)
	at java.util.concurrent.FutureTask.get(Unknown Source)
	at com.hof.pool.JDBCConnection.C(JDBCConnection.java:390)
	at com.hof.pool.JDBCConnection.D(JDBCConnection.java:370)
	at com.hof.pool.DBConnectionPool.E(DBConnectionPool.java:1578)
	at com.hof.pool.DBConnectionPool.A(DBConnectionPool.java:232)
	at com.hof.pool.DBConnectionPool$_A.run(DBConnectionPool.java:2002)
	at java.util.TimerThread.mainLoop(Unknown Source)
	at java.util.TimerThread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:710)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.clearWarnings(SQLServerConnection.java:2932)
	at com.hof.util.i4Connection.clearWarnings(i4Connection.java:95)
	at com.hof.pool.JDBCConnection$3.call(JDBCConnection.java:383)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
and of course that error supports the theory that the problem is caused by the SQL Server DBMS closing the connection, not Yellowfin.

But anyway, we will certainly learn more when you send us the Profiler trace log.


regards,

David

photo
1

Hi Dave,

Here is the tracelog of the database. Let me know if that helps.

Regards,

Bharath

photo
1

Screenshot from database

photo
1

Hi Bharath,

thanks for the tracelog. I searched through it and found that every 5 minutes Yellowfin was correctly sending across queries such as:

DELETE FROM Event
WHERE GMTDateTime < 20180605093700

DELETE FROM EventArchive
WHERE GMTDateTime < 20180605093700	
and their durations ranged from about 10 seconds to 50 seconds. (I have attached a short video of this, "DELETE_queries.mp4")


So from this perspective it can be seen that Yellowfin's EventArchiveTask is correctly creating queries such as the above and sending them onto the AR JDBC Driver, and then the driver is correctly passing them onto the SQL Server DBMS.

But as to what happens in the database I can't explain.

The trace log shows that the queries ran to completion (because they have values in the "Duration" column), but as to why SQL Server doesn't actually delete the rows I do not know.

This is something to explain to your DBA and to ask their opinion.

Please let me know what they say.


thanks,

David

photo
1

Hi Dave,

Good news!

I sent you the TaskSchedule table last month (7th. May), and this was the output:

1SYSTEMTASKEventArchiveTask01AUSTRALIA/SYDNEYDAILYNULL02,0181E+132018-05-06 00:00:00.00056088FAILUREcom.microsoft.sqlserver.jdbc.SQLServerException: socket closed0NULLNULLNULLI’ve run the same query today, and it seems like now it’s finally working.

IpOrg ScheduleSubjectCode ScheduleUnitCode ScheduleUnitId ScheduleIsOn LocalTimezoneCode FrequencyTypeCode FrequencyCode FrequencyUnit LastRunGMTDateTime LastRunDate LastRunTime LastRunStatus LastRunErrorDesc ScheduledLocalRunTime PriorityCode PrioritySetByCode LastRunUUID

1 SYSTEMTASK EventArchiveTask 0 1 AUSTRALIA/SYDNEY MINUTES NULL 1 20180607113200 2018-06-07 00:00:00.000 41520 SUCCESS NULL 0 NULL NULL NULL

The LastRunStatus is finally showing SUCCESS. I have no idea when this happens, but sometime after that I change the parameters:

UPDATE TaskSchedule SET FrequencyTypeCode = 'MINUTES', FrequencyUnit = 1 WHERE ScheduleUnitCode = 'EventArchiveTask'

Once this is OK, should I change this back to daily?

UPDATE TaskSchedule SET FrequencyTypeCode = DAILY, FrequencyUnit = 1 WHERE ScheduleUnitCode = 'EventArchiveTask'


Regards,

Bharath

photo
1

Hi Bharath,

yes that is very good news indeed!

And for me, that is further confirmation that the issue was at the SQL Server end, not Yellowfin.

Regarding the frequency of the job, that is entirely up to you, by default the Yellowfin installer sets it to DAILY, and most clients seem happy with that.

regards,

David

photo
1

Hi,


Another question, is there any way to disable event log entirely? And by disable, I mean that make YF not log into the event table at all.

-Bharath

photo
1

I think I can remove the entry from the log4j.properties file:

log4j.category.com.hof.servlet.EventArchiveTask=DEBUG

photo
1

Hi Bharath,

no, the log4j.properties file is all about writing to the log files found in <yellowfin>\appserver\logs, not writing to the Event table.

Why not create a database "AFTER INSERT" trigger to delete new Event records, especially as that way you could decide which (if any) of the types of Event records you might want to actually keep? (for example, delete most records except for ones like EventCode = 'RPTDELETE' )

regards,

David

photo
1

Hi Dave,

Thanks for the response. Clearly the INSERTINTOConfigurationVALUES (1,'SYSTEM','DISABLEREPORTEVENTS','true');


Didn’t work. So if have any other suggestions to just turn off event table?


As you can see the screenshot, the ID is up to 60M records in terms of ID, so a lot of log in generated. And we have figured out that we don’t need the event table at all.


Is there a way to disable event table ?

Regards,

Bharath

photo
1

Hi Bharath,

I have tested DISABLEREPORTEVENTS over here and it is working correctly.

You must remember that DISABLEREPORTEVENTS doesn't stop ALL events from being recorded in the EVENT table. Instead, as its name implies, it only stops the events to do with running reports from being recorded (i.e. only EventCode = 'RPTRUN' or 'DASHRUN'), and seeing as that is only a fraction of the total amount of EventCodes that get recorded, then this option is not what you are looking for.

Also, I tested setting EVENTARCHIVEMAXDAYS and EVENTMAXDAYS to 0, but unfortunately that didn't switch of creating new records in the Event table either.

So in summary, there is no way to directly disable writing to the event table.

However, what did you think of my previous suggestion of creating a database trigger? I know this is a workaround, but it would certainly do the job.

regards,

David

photo
1

Hi Dave,

Thank you, setting the event days to 1 is enough for me. And as long as the TaskSchedule is running without socket closed error, the event table will never have more the 100 000 records.


What worried me is that the id is already up to 60M, is there any upper limit? I guess I’m satisfied with the TaskSchdule job running as it should.

-Bharath

photo
1

Hi Bharath,

the ScheduleUnitId is of datatype "int" which for SQL Server has an upper limit of 2,147,483,647. If you are concerned then I guess you could always change the datatype to bigint which has an upper limit of 9,223,372,036,854,775,808

regards,

David

photo
1

Hi Dave,

It will take me 30 year to reach 2.147.483.647 records, so we are good to go now.

Big Thank you for all the help.

-Bharath

photo
1

You're welcome!

Interestingly, by that time the world population will be about 10,000,000,000 so that means we would definitely need a bigint identifier for our Planet Earth identity cards!

regards,

David

Leave a Comment
 
Attach a file