SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
DBAudit - Alert Center freezes when running reports

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
DBAudit - Alert Center freezes when running reports
Author Message
VadimE



Joined: 06 Nov 2012
Posts: 7
Country: Romania

Post DBAudit - Alert Center freezes when running reports Reply with quote
Hello

We have a problem with reports generation on DBaudit 4.2 with alert center.

We can generate simple reports send by email (we choose to zip them) like Failed Connection Attempts / Database schema changes.

But when we try to generate any report which is supposed to contain useful data for us like "text of sql queries" "object access and operation", even for one day everything freezes and we don't get a report even after some hours.

We tried to add -Xms parameter between java and -jar parameter - java -Xms1024m -jar alertCenter.jar /JOB 123 , but it didn't solve the problem.

We run a central repository and try to generate the reports for databases hosted on another servers, the SQL server is 2008R2 Enterprise.

Any ideas?

Thanks
Tue Nov 06, 2012 7:33 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6501

Post Reply with quote
Hi,

Let's figure out where the report get's stuck. Could you please post the log of the report generation job. There should be a file in the Alert Center Data sub-folder named 123.log. Could you please take a look at that file and tell us what you see there, or better post the entire content of that file, removing any sensitive database connection info, if there is any.
Wed Nov 07, 2012 11:12 am View user's profile Send private message
VadimE



Joined: 06 Nov 2012
Posts: 7
Country: Romania

Post error log for report generation Reply with quote
This is our job log:

Getting properties of job #48...
Report type: RDA
Report description: Text of SQL Queries (Yesterday)
Report recipient: aaa
Report sender: aaa
Database profile: pelicandb_ADM
SQL: /R TextOfSQLQueries /S @D1 DBAudit
Getting properties of database profile aaaaa...
JDBC interface: ./drivers/mss/sqljdbc.jar
JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Database URL: jdbc:sqlserver://PELICANDB:1433;databaseName=DBAudit;SelectMethod=cursor
User: sa
Connecting to database...
Connected
Running job #48...
Command: /R TextOfSQLQueries /S @D1 DBAudit
Executing report...
Reporting period: Tue Nov 06 23:47:36 EET 2012 - Thu Nov 08 23:47:36 EET 2012
Reporting period: Tue Nov 06 23:47:36 EET 2012 - Thu Nov 08 23:47:36 EET 2012
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at com.sun.org.apache.xml.internal.utils.FastStringBuffer.append(Unknown Source)
at com.sun.org.apache.xml.internal.dtm.ref.sax2dtm.SAX2DTM.characters(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.dom.SAXImpl.characters(Unknown Source)
at com.softtreetech.dbaudit.xml.AbstractReportFormatter.putColumn(AbstractReportFormatter.java:83)
at com.softtreetech.dbaudit.xml.ReportFormatterFormated.printRows(ReportFormatterFormated.java:196)
at com.softtreetech.dbaudit.xml.ReportFormatter.buildReportBody(ReportFormatter.java:52)
at com.softtreetech.dbaudit.xml.AbstractReportFormatter.parse(AbstractReportFormatter.java:44)
at com.sun.org.apache.xalan.internal.xsltc.dom.XSLTCDTMManager.getDTM(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.dom.XSLTCDTMManager.getDTM(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.getDOM(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.transform(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.transform(Unknown Source)
at com.softtreetech.dbaudit.xml.XMLReportTransformer.buildReportXML(XMLReportTransformer.java:52)
at com.softtreetech.dbaudit.xml.AbstractReportFormatter.buildReport(AbstractReportFormatter.java:89)
at com.softtreetech.dbaudit.reports.MssqlReports.doTextOfSQLQueriesReport(MssqlReports.java:193)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.executeReport(AbstractCommandHandler.java:198)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.execute(AbstractCommandHandler.java:88)
at com.softtreetech.dbaudit.command.LegacyCommandHandler.handleCommand(LegacyCommandHandler.java:33)
at AlertJob.main(Unknown Source)
23:51:00,725 [WARN ] ResultSet not closed before garbage collection


Kind Regards
Wed Nov 07, 2012 6:17 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6501

Post Reply with quote
It looks like you have a lot of data collected between Nov 06 23:47:36 EET 2012 and Nov 08 23:47:36 EET 2012. An OutOfMemory error occurs in com.sun.org.apache.xml.internal.utils while the process is trying to save the returned result set as an internal XML file before converting it to the final format.

This report requires more memory allowed to the process. Try changing the command line for job #48
java -Xms2048m -jar alertCenter.jar /JOB 48

Please be sure you modify the correct job, your log refers to job #48, your previous post refers to job #123, there seems to be some discrepancy.


Also please try running the following query in your PELICANDB database server

SELECT count(*) FROM DbAudit.db_audit.sys_audit_trail WHERE event_time BETWEEN '2012-11-07' AND '2012-11-09'

How many records does it find?
Wed Nov 07, 2012 7:01 pm View user's profile Send private message
VadimE



Joined: 06 Nov 2012
Posts: 7
Country: Romania

Post new alert cener error Reply with quote
Hello

Another error after changing the parameter for desired reports:

Alert Center error: Unable to run job#AAA java.net.SocketException: Connection reset by peer: socket write error

I have restarted the server, no luck.

Thanks
Thu Nov 08, 2012 7:47 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6501

Post Reply with quote
In the Alert Center Please check job specific log Data/AAA.log. What does it say there? Specifically, what happens before the "Connection reset by peer: socket write error" error is logged to the scheduler.log?
Thu Nov 08, 2012 9:30 am View user's profile Send private message
VadimE



Joined: 06 Nov 2012
Posts: 7
Country: Romania

Post DBAudit - Alert Center freezes when running reports Reply with quote
Hello

The job log says:

Queue SERVERNAME maximum allowed size exceeded. Job RDA - Text of SQL Queries (yesterday) not queued.


Thanks
Thu Nov 08, 2012 9:18 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6501

Post Reply with quote
I see. I guess the other error is just a side effect of this one as a result of the job getting aborted. Please check queue sizes, make sure they are set to some reasonable value, for example, 100 or 1000. If you are not sure where to check, please let us know.
Thu Nov 08, 2012 11:24 pm View user's profile Send private message
VadimE



Joined: 06 Nov 2012
Posts: 7
Country: Romania

Post DBAudit - Alert Center freezes when running reports Reply with quote
Hello

Another error now

Alert Center error: Unable to run job#AAA Job execution failed. See job log file for details.

Now the job log file is even not made, but alert center scheduler say that the job started and finished in the same second.


Thanks for your help.
Fri Nov 09, 2012 6:07 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6501

Post Reply with quote
Hello,

Please check the job command line. If you have modified it manually, the job cannot start. If you indeed the changed the command line, please paste the complete command here so we can verify it.
Fri Nov 09, 2012 6:15 pm View user's profile Send private message
VadimE



Joined: 06 Nov 2012
Posts: 7
Country: Romania

Post DBAudit - Alert Center freezes when running reports Reply with quote
Ok
Now you lost me completely.

Please tell me how and where I can check the job command line.

The only thing which I made is to edit from Alert Center Scheduler the job properties wizard step 2, from:
java -jar alertCenter.jar /JOB AAA , to
java -Xms2048m -jar alertCenter.jar /JOB AAA

What else I should do ?
Fri Nov 09, 2012 6:23 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6501

Post Reply with quote
Thank you. That's what I meant.

Please change java -Xms2048m -jar alertCenter.jar /JOB AAA back to java -jar alertCenter.jar /JOB AAA and run the job again. Do you get the same error?
Fri Nov 09, 2012 7:25 pm View user's profile Send private message
VadimE



Joined: 06 Nov 2012
Posts: 7
Country: Romania

Post DBAudit - Alert Center freezes when running reports Reply with quote
The error is back, it is a bit different:

Connecting to Alert Center...

******************************************************
* Copyright (C) 2000-2007 SoftTree Technologies, Inc.*
******************************************************
* 24x7 Scheduler Java API v4.1. *
******************************************************
* Unauthorized use or distribution of this program *
* may result in severe civil and criminal penalties, *
* and will be prosecuted to the maximum extent *
* possible under the law. *
******************************************************

Getting properties of job #AAA...
Report type: RDA
Report description: Text of SQL Queries (Yesterday)
Report recipient: aaaaaaaaaaaaaa
Report sender: aaaaaaaaaaaaaa
Database profile: aaaaaaaaaaaaaa
SQL: /R TextOfSQLQueries /S @D1 DBAudit
Getting properties of database profile pelicandb_ADM...
JDBC interface: ./drivers/mss/sqljdbc.jar
JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
Database URL: jdbc:sqlserver://PELICANDB:1433;databaseName=DBAudit;SelectMethod=cursor
User: sa
Connecting to database...
Connected
Running job #56...
Command: /R TextOfSQLQueries /S @D1 DBAudit
Executing report...
Reporting period: Fri Nov 09 01:44:14 EET 2012 - Sun Nov 11 01:44:14 EET 2012
Reporting period: Fri Nov 09 01:44:14 EET 2012 - Sun Nov 11 01:44:14 EET 2012
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at com.microsoft.sqlserver.jdbc.TDSPacket.<init>(IOBuffer.java:3114)
at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:3269)
at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:4437)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$CursorFetchCommand.doExecute(SQLServerResultSet.java:4750)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.doServerFetch(SQLServerResultSet.java:4787)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:980)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.softtreetech.dbaudit.ResultSetHandler.invoke(ResultSetHandler.java:33)
at $Proxy0.next(Unknown Source)
at com.softtreetech.dbaudit.reports.ResultSetResult.next(ResultSetResult.java:73)
at com.softtreetech.dbaudit.xml.ReportFormatter.buildReportBody(ReportFormatter.java:43)
at com.softtreetech.dbaudit.xml.AbstractReportFormatter.parse(AbstractReportFormatter.java:44)
at com.sun.org.apache.xalan.internal.xsltc.dom.XSLTCDTMManager.getDTM(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.dom.XSLTCDTMManager.getDTM(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.getDOM(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.transform(Unknown Source)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.transform(Unknown Source)
at com.softtreetech.dbaudit.xml.XMLReportTransformer.buildReportXML(XMLReportTransformer.java:52)
at com.softtreetech.dbaudit.xml.AbstractReportFormatter.buildReport(AbstractReportFormatter.java:89)
at com.softtreetech.dbaudit.reports.MssqlReports.doTextOfSQLQueriesReport(MssqlReports.java:193)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.executeReport(AbstractCommandHandler.java:198)
at com.softtreetech.dbaudit.commandhandler.AbstractCommandHandler.execute(AbstractCommandHandler.java:88)
at com.softtreetech.dbaudit.command.LegacyCommandHandler.handleCommand(LegacyCommandHandler.java:33)
at AlertJob.main(Unknown Source)
01:50:33,406 [WARN ] ResultSet not closed before garbage collection
Fri Nov 09, 2012 8:01 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6501

Post Reply with quote
That's good. At least we now know that adding "-Xms2048m" somehow prevents the job from starting. I'm not sure why, it seems to be correct to me.

Without that extra parameter the job can run with default memory settings, but then it runs out of memory leading to "java.lang.OutOfMemoryError: Java heap space"

How many records do you have in db_audit.sys_audirt_trail table for a single day like yesterday?
Fri Nov 09, 2012 9:11 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.