Hello, so I recently ran into a situation where a live database was emptied. I had a backup but that only got me so far, and I lost some records since the last backup. Is there a log of all the queries that are executed against the database? Or is there some way I can easily log queries in real-time?

Also, general question, if you have a table marked as exclusive and open it in another process as read-only will you still get a table marked exception?

Marcello


I don't think there's a log of queries. For the future you could look at the replication / publishing feature and use it as an audit log.

Roy Lambert

Marcello

>>Is there a log of all the queries that are executed against the database? Or is there some way I can easily log >>queries in real-time?

There is a configuration data-table:

Configuration.LoggedStatements

I use a simple statement:

SELECT
 "ExecutedOn" as DateDone,
 "LinkID",
 "LinkType",
 "LinkTable",
 "Process",
 "User",
 "SessionID",
 "SessionName",
 CAST("SQL" AS VARCHAR(250)) as "ShortSQL",
 "ExecutionTime",
 "RowsAffected",
 "SQL"
FROM Configuration.LoggedStatements LS

to monitor SQL that has run.

--

Note that LoggedStatements only functions if it is switched on on the server. I run the following job to ensure this happens:

CREATE JOB "StartUp"
RUN AS "Administrator"
FROM DATE '2019-03-13' TO DATE '2025-03-13'
AT SERVER START
BETWEEN TIME '00:33:04' AND TIME '23:33:04.999'
BEGIN
    
USE "<MyDatabase>";

EXECUTE IMMEDIATE
' ENABLE STATEMENT LOGGING ';

--

Statement Logging has some switches you can add to determine what it logs and how many statements it stores. If you are logging a lot it is sensible to run a procedure to dump the contents out somewhere periodically. I use a data-table in a system-database.

Adam


Is this C/S only? I've just tried it on some of my development F/S databases and its empty, and these three columns "LinkID", "LinkType", "LinkTable" don't exist

Roy Lambert

Roy


>>Is this C/S only? I've just tried it on some of my development F/S databases and its empty,

As one of the steps to use it is to run a statement on the server to switch on Statement Logging, I am guessing this is probably a "yes" ... but I don't know for sure

>>and these three columns "LinkID", "LinkType", "LinkTable" don't exist

Argh! Sorry, my bad. These three columns are actually from my own database, and are JOINed to the Configuration.LoggedStatements table. I should have deleted them from my post.

SELECT * FROM Configuration.LoggedStatements

is the place to start ...

On 1/20/2020 7:09 AM, Roy Lambert wrote:

> Is this C/S only? I've just tried it on some of my development F/S databases and its empty, and these three columns "LinkID", "LinkType", "LinkTable" don't exist
>

Should be in the engine so available on f/s also though not sure.

It was added back in 2.29 for helping with slow queries (default
settings log if it takes 30 sec or longer) but you can dial it down.
Looks like minimum is still 1 sec execution time  so not sure what
happens with very short queries.

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=ENABLE_STATEMENT_LOGGING

Raul

Looks like its time for a re-read of the manual

Roy Lambert

Marcello,

<< Hello, so I recently ran into a situation where a live database was emptied. I had a backup but that only got me so far, and I lost some records since the last backup. Is there a log of all the queries that are executed against the database? Or is there some way I can easily log queries in real-time? >>

You can turn on tracing in ElevateDB, but that's not really a long-term solution due to the amount of trace data generated.  Are you looking to implement a general audit trail for the database ?  As Raul mentioned, the statement logging is what you need, but it is bounded by the minimum execution time parameter.  Furthermore, the statement logging doesn't persist this information in long-term storage.

<< Also, general question, if you have a table marked as exclusive and open it in another process as read-only will you still get a table marked exception? >>

Any time that you open a table exclusively in one session/application, it will then be unavailable for opening elsewhere.

Tim Young
Elevate Software
www.elevatesoft.com