The Dynamics Explorer

Introduction

On occasion a batch can be accidentally deleted from the system, and for one reason or another it would be interesting to find out which user did this. In this post I aim to show how this might be possible using some SQL functions to query the SQL transaction log. (I appreciate there are options available to prevent users deleting batches however I’m not covering this)

Pre-requisite and Considerations

One pre-requisite is that the SQL database must be in the FULL recovery model. This won’t work in the SIMPLE model as the transaction log information is lost at each checkpoint.

Another consideration you must make is that the process of posting a batch in Dynamics GP effectively deletes the batch header from the SY00500 table as well. Therefore, the result set from the query could include both genuine deletes, caused by posting batches, and deletes that have happened accidentally. However if you know a little detail about the batch that you wish to track like the type of batch that was deleted, and approximately the number of transactions in the batch, I’m hoping the result you get back from this query will assist you in making an educated guess as to if this is actually the deleted batch you were looking for.

The Theory

In SQL every INSERT\UPDATE\DELETE operation is first logged in the transaction log before being written to the database. Therefore, theoretically using a SQL function you can query the transaction log looking for DELETEs to tables and objects.

I mentioned earlier that the database must be in the FULL recovery model. To expand on this if the database is in the SIMPLE recovery model then after the INSERT\UPDATE\DELETE transaction commits, and a checkpoint occurs, that transaction log information is lost. Therefore you won’t be able to query the transaction log for deletes when the database is in SIMPLE recovery model.

However when the database is in the FULL recovery model, the log information isn’t lost until a LOG backup is performed. Therefore using a SQL function you can query the log looking for deletes.

Finally, all is not lost if you have performed a log backup since the delete occurred as you can use another SQL function to query the transaction log backup file that resides on disk. You just need to know the file name of the backup and its location on disk.

Use FN_DBLOG to query the transaction log

To demonstrate the process, I first created the following batch in Dynamics GP which contains one transaction.

I then deleted the batch from Dynamics GP and using the query below I can identify SQL transactions that include a delete on the batch headers table. (SY00500). Please note this needs running the company database.

SELECT [transaction name], 
       users.NAME, 
       [transaction id], 
       operation, 
       allocunitname, 
       [begin time], 
       [end time], 
       [transaction name] 
FROM   Fn_dblog(NULL, NULL) trnlog 
       LEFT JOIN sys.sysusers users 
              ON trnlog.[transaction sid] = users.sid 
WHERE  [transaction id] IN (SELECT [transaction id] 
                            FROM   Fn_dblog(NULL, NULL) 
                            WHERE  operation IN ( 'LOP_DELETE_ROWS' ) 
                                   AND allocunitname LIKE '%sy00500%')   

This gives the following results

As you can from the results above there’s a SQL Transaction that includes a deletion to the batch header table (SY00500) and associated objects (i.e. the indexes). This is SQL Transaction ID 0000-00045d05. As part of the same transaction you can see deletes to GL tables and objects therefore its clear this is a GL batch that was deleted. You can also see the user who deleted the batch in the “name” column.

Use FN_DUMP_DBLOG to query the transaction log

If a transaction log backup has occurred since the batch was deleted, you won’t be able to use the query above as the log will have been cleared. However you can query the SQL transaction log backup file directly using a different SQL function called FN_DUMP_DBLOG.

To test this I performed a transaction log backup and gave it a file name of C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\TWO18_3.trn and used the query below to look for the same delete I performed earlier. (please note in this query I had to change a few things as the object name didn’t work without joining to another system table)

SELECT [transaction name], 
       users.NAME, 
       [transaction id], 
       operation, 
       Object_name(part.object_id) AS ObjectName, 
       [begin time], 
       [end time], 
       [transaction name] 
FROM   Fn_dump_dblog (NULL, NULL, N'DISK', 1, 
N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\TWO18_3.trn' 
, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) trnlog 
LEFT JOIN sys.sysusers users 
       ON trnlog.[transaction sid] = users.sid 
LEFT JOIN sys.partitions part 
       ON trnlog.partitionid = part.partition_id 
WHERE  [transaction id] IN (SELECT [transaction id] 
                            FROM   Fn_dump_dblog (NULL, NULL, N'DISK', 1, 
N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\TWO18_3.trn' 
, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) trnlog 
LEFT JOIN sys.partitions part 
       ON trnlog.partitionid = part.partition_id 
 WHERE  operation IN ( 'LOP_DELETE_ROWS' ) 
        AND Object_name(part.object_id) = 'sy00500')   

This gave me the output below which is the same as the original output. Its just the data resides in the transaction log backup file now. (i.e. its the same SQL Transaction ID)

If you don’t find the delete you can just alter the backup file name and keep searching through your backups.

Conclusion

Therefore although not 100% ideal this is one method that can assist in identifying a user that has deleted a batch in Dynamics GP.

You can also use the same method to find out which user deleted other transactions you just need to alter the object name.

I’d also like to note that although I have tweaked the SQL scripts I’ve used in this post myself, I most certainly had to stand on the shoulders of several SQL giants along the way. I’m sure everyone is aware the SQL community is also awesome at sharing information to help others like me!

Thanks for reading