Is it OK to delete rows from Sysdatabaselog directly in SQL Server

(0) Share

Report

Posted on by Community Member

Just wanting to confirm if I can use a SQL job to delete data from Sysdatabaselog periodically. I know the official way would be to set up a batch job, but just checking to see if it's OK to do it ad hoc with SQL.

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

Martin Dráb 237,803 Most Valuable Professional on at

Like (3)

Report

There are no delete actions, delete() isn't overridden and nobody will log it. But SysDatabaseLogCleanup (the class that you should actually use) includes some logic regarding electronic signature; SQL query would also bypass AOS authorization and AX cache won't be aware of the deletion.

My recommendation is sticking to the SysDatabaseLogCleanup class. It's there for exactly this purpose and you can run it on ad-hoc basis as well.

Was this reply helpful? Yes No
Ebbe Høeg Jensen 150 on at

Like (0)

Report

I checked table SIGSignatureLog and table SysSignatureSetup. They were empty. As the logging has been disabled for the tables I want to delete records for in SysDatabaseLog table, I see no problem deleting the SysDatabaselog records directly in SQL Server. We got millions of these records.

Was this reply helpful? Yes No
Aparisi82 2,188 on at

Like (0)

Report

Hello,

did you eventually run the deletion from sql using delete _from or using the clean up functionality?

I tested the same in a TEST environment and it took almost 2 hours for 20 million records.

The issue is that it will lock the table and potentially stop any AX process that needs to delete,update,insert on the sysdatabaselog.

How did you do it? DId you plan a maintenance window?

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

delete_from is not an optimal solution to be honest, unless you want to remove a subset of records only. If you want to remove all entries, and you do not have connected records like the above mentioned signatures, why not just truncate the table? It would be instantaneous.

docs.microsoft.com/.../truncate-table-transact-sql

If you want to keep entries from the past year but nothing before, I would recommend a sliding window partitioning, then truncate old partition data.

Was this reply helpful? Yes No
Aparisi82 2,188 on at

Like (0)

Report

Thanks for the quick reply.

Actually I need to delete only a certain amount of records ( assigned to a specific user).

Is there another way to delete these records quickly>

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

See above, by setting up SQL partitions for date range/user (or however you want to split it).

Was this reply helpful? Yes No
Aparisi82 2,188 on at

Like (0)

Report

Interesting.. I will play with this. Thanks and regards

Was this reply helpful? Yes No
Suggested answer

Ebbe Høeg Jensen 150 on at

Like (0)

Report

I'm sorry but I can't remember what I did exactly. But I believe I did some batching to prevent locking the tables. Look at this stackoverflow.com/.../will-delete-sql-locks-the-table-which-prevents-new-data-insert-into-sql-server-2

And it was carried through outside working hours.

Best regards, Ebbe

Was this reply helpful? Yes No
Ebbe Høeg Jensen 150 on at

Like (1)

Report

Now I've found the script I used for deleting the 125 mio. records. Sample:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
BEGIN
    BEGIN TRANSACTION

    -- Delete some small number of rows at a time
    DELETE TOP (10000) SYSDATABASELOG
    WHERE TABLE_ = 110188 -- table OioublReceivedDocs

    SET @Deleted_Rows = @@ROWCOUNT;

    COMMIT TRANSACTION
    CHECKPOINT -- for simple recovery model

END

Best regards, Ebbe

Was this reply helpful? Yes No