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

### 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](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)