DBAudit - Alert Center freezes when running reports

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

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.

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

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?

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

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?

Hello

The job log says:

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

Thanks

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.

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.

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.

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 ?

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?

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

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?