Increase Airflow logging coverage, review existing logs and add missing essential logs

[omkar-foss]

Description

As per users' feedback in the Airflow Debugging Survey 2024, about 42% of respondents mentioned that logs are missing or non-existent in some cases.

Use case/motivation

Goal for this issue is to review the existing Airflow logs coverage and enhance it by adding logs wherever appropriately required.

Related issues

Parent Issue: https://github.com/apache/airflow/issues/40975

Are you willing to submit a PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [x] I agree to follow this project's Code of Conduct