---
title: "How to log SQL activity"
canonical: "https://support.appfire.com/space/SUPPORT/89139729/How%20to%20log%20SQL%20activity"
format: markdown
---
On this page Log SQL statements Administrators can optionally log all SQL statements used in the  SQL  macros. This option is enabled by adding configuration in the  ../WEB-INF/classes/log4j.properties  file. In addition, warnings are issued when the default row limit is exceeded. Time logging is also available since  version 5.1.0 . Add to ../WEB-INF/classes/log4j.properties # SQL statement logging
log4j.appender.bobswiftsql=org.apache.log4j.RollingFileAppender
log4j.appender.bobswiftsql.File=${catalina.home}/home/logs/bobswift-sql.log
log4j.appender.bobswiftsql.Threshold=INFO
log4j.appender.bobswiftsql.MaxFileSize=20480KB
log4j.appender.bobswiftsql.MaxBackupIndex=10
log4j.appender.bobswiftsql.layout=com.atlassian.confluence.util.PatternLayoutWithContext
log4j.appender.bobswiftsql.layout.ConversionPattern=%d [%t] %m%n

log4j.logger.org.swift.confluence.sql.SqlMacro.statement=INFO, bobswiftsql
log4j.additivity.org.swift.confluence.sql.SqlMacro.statement=false

log4j.logger.org.swift.confluence.sql.SqlQueryMacro.statement=INFO, bobswiftsql
log4j.additivity.org.swift.confluence.sql.SqlQueryMacro.statement=false

log4j.logger.org.swift.confluence.sql.SqlFileMacro.statement=INFO, bobswiftsql
log4j.additivity.org.swift.confluence.sql.SqlFileMacro.statement=false
 
# SQL time logging
log4j.logger.org.swift.confluence.sql.SqlMacro.timing=INFO, bobswiftsql
log4j.additivity.org.swift.confluence.sql.SqlMacro.timing=false

log4j.logger.org.swift.confluence.sql.SqlQueryMacro.timing=INFO, bobswiftsql
log4j.additivity.org.swift.confluence.sql.SqlQueryMacro.timing=false
 
log4j.logger.org.swift.confluence.sql.SqlFileMacro.timing=INFO, bobswiftsql
log4j.additivity.org.swift.confluence.sql.SqlFileMacro.timing=false Using another Windows drive A direct Windows path can be specified with a drive but you must use forward slashes (or double back slashes according to   this reference ). Add INFO logging at runtime Go to  Administration > Logging and Profiling . Find the  Add new entry  area. Add  INFO  entries for the following:  org.swift.confluence.sql . Log statements appear in the standard Confluence log file.  Logging occurs immediately, but only lasts until Confluence is restarted. Add DEBUG logging at runtime Go to  Administration > Logging and Profiling . Find the  Add new entry  area near the top of the page. Add the following entries for the Class/Package Names:  org.swift.confluence.sql . Select  DEBUG  for the  New Level  value and click  Add entry : Log statements appear in the standard Confluence log file.  Logging occurs immediately, but only lasts until Confluence is restarted. Ensure to go back and remove the entry that was added after you completed triaging your issue.  Use logging when establishing or lowering row limit Setting or lowering the row limit in installation may cause a few existing SQL queries to show only the limited number of rows. Use logging to track usage that exceeds the limit. Investigate queries that exceed the default limit for correctness. If correct, use the  limit  parameter to prevent further warnings. Additivity Setting additivity to false prevents SQL from being logged to your main Confluence log. Example log 2012-10-20 05:14:57,019 [http-8116-4] userId: admin, dataSource: testDS, contentId: 91816371, spaceKey: zsql, title: dateRows, sql: DELETE from test_date;
            INSERT INTO test_date VALUES (1, date '2009-09-30', timestamp '2009-09-30 01:00', timestamp '2009-09-30 01:00', time '05:45:01', time '14:20');
            INSERT INTO test_date VALUES (2, date '2010-08-10', timestamp '2010-08-10 13:00', timestamp '2010-08-10 13:00', time '13:45:01', time '02:20');
            INSERT INTO test_date VALUES (3, date '2008-12-31', timestamp '2010-08-10 01:01', timestamp '2010-08-10 01:01', time '13:05:00', time '14:35');
            INSERT INTO test_date VALUES (4, date '2010-03-01', timestamp '2010-08-10 01:00', timestamp '2010-08-10 01:00', time '23:55:00', time '11:35');
            INSERT INTO test_date VALUES (5, date '2010-03-05', timestamp '2009-08-10 01:00', timestamp '2009-08-10 01:00', time '11:55:00', time '02:55');
            INSERT INTO test_date VALUES (6, date '2008-01-30', timestamp '2009-08-10 01:00', timestamp '2009-08-10 02:00', time '08:00:01', time '02:10');
            INSERT INTO test_date VALUES (7, date '2009-03-01', timestamp '2009-08-10 01:00', timestamp '2009-08-10 01:00', time '07:45:01', time '23:59');
            SELECT * from test_date;
2012-10-20 05:14:58,137 [http-8116-4] userId: admin, dataSource: testDS, contentId: 91816372, spaceKey: zsql, title: dateSort, sql: SELECT * from test_date Timing logging 2012-10-21 19:29:48,113 [http-8116-4] statement time: 2293 ms, total time: 2300 ms, sql: select ... Interpret logging Statements are logged prior to the database being called and includes the space key and page title the macro was on.  Time is logged after the database operation completes. The fields are defined as follows: Statement time Difference between making the request to the database and database completing the query and return a successful response Total time Difference between making the request to the database and retrieving all result sets, constructing tables, and any wiki rendering required