No your theory is wrong.
Dirty pages can be written to disc even if the transaction is not yet committed. However it is ensured that they cannot be written before the last transaction log entry that modified the page has been written to disc.
The transaction log records do contain sufficient information both for redo and undo (except for in tempdb where only undo is necessary). If you decide to rollback the transaction then nothing is deleted from the log. Instead compensation log records are written to the log indicating this.
Have you tried running a full backup followed by another transaction log backup? What error messages are you getting?
We know the logs backup fails, but if it's in simple recovery mode and
the database is backed up but the log back up fails would I be able to
recover the database? By recover lets get this straight, I'm talking
about getting the thing working again by simple restoring from my last
back up?
If the database fails, you can recover the database with the last full backup regardless of whether it's set to full or simple recovery. Simple recovery doesn't support log backups at all. So basically, you'd lose everything from your last full backup forward.
So wouldn't creating new log file and removing the old log file (but
leaving the old file on the disk) be the thing to do?
NO. DO NOT DELETE YOUR LOG FILE. You'll probably kill your database and the symptom you're trying to fix is that it won't back up properly.
I'm going to recommend this article, "A beginner’s guide to SQL Server transaction logs." Especially this bit:
Can SQL Server database work without a transaction log?
No, that is not possible due to the SQL Server design and ACID
compliance. ACID stands for atomicity, consistency, isolation, and
durability.
Also this article on Full vs. Simple recovery.
To crib from this article:
- Run
dbcc checkdb on the database
- Stop all user activity in the database
- Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up) (which you've done, yes)
- Switch to the FULL recovery model
- Take a full database backup (thus starting a new log backup chain)
- Start taking log backups
Best Answer
This isn't logged anywhere (by default). You can investigate the "server audit" functionality and see if that gives you what you want.