Author |
Message |
VadimE
Joined: 06 Nov 2012 Posts: 7 Country: Romania |
|
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
|
|
Tue Nov 06, 2012 7:33 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
VadimE
Joined: 06 Nov 2012 Posts: 7 Country: Romania |
|
error log for report generation |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
VadimE
Joined: 06 Nov 2012 Posts: 7 Country: Romania |
|
new alert cener error |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
VadimE
Joined: 06 Nov 2012 Posts: 7 Country: Romania |
|
DBAudit - Alert Center freezes when running reports |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
VadimE
Joined: 06 Nov 2012 Posts: 7 Country: Romania |
|
DBAudit - Alert Center freezes when running reports |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
VadimE
Joined: 06 Nov 2012 Posts: 7 Country: Romania |
|
DBAudit - Alert Center freezes when running reports |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
VadimE
Joined: 06 Nov 2012 Posts: 7 Country: Romania |
|
DBAudit - Alert Center freezes when running reports |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
|