The optimization process and recommendations:

1. Avoid LIKE Searches With Leading Wildcard (query line: 25): The database will not use an index when using like searches with a leading wildcard (e.g. '%ACQUIRE_LOCK_SCH_M OBJECT%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').

The optimized query:

SELECT
        NextLSN = MAX(fn.[Current LSN]),
        Databasename = DB_NAME() 
    FROM
        fn_dblog(NULL,
        NULL) fn 
    LEFT JOIN
        sys.allocation_units au 
            ON fn.AllocUnitId = au.allocation_unit_id 
    LEFT JOIN
        sys.partitions p 
            ON p.partition_id = au.container_id 
    LEFT JOIN
        sys.objects so 
            ON so.object_id = p.object_id 
    WHERE
        (
            (
                Operation IN (
                    'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW', 'LOP_DELETE_ROWS', 'LOP_BEGIN_XACT', 'LOP_COMMIT_XACT'
                ) 
                AND so.is_ms_shipped = 0
            ) 
            OR (
                [Lock Information] LIKE '%ACQUIRE_LOCK_SCH_M OBJECT%'
            )
        )