Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?
Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.
A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.
Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.
For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.
For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.
Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.
Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)
It sounds like you've got a huge transaction that has remained open which has done a lot of work. Run DBCC OPENTRAN on the database and see how long the oldest transaction has been open for. You'll probably need to kill that transaction (or have the user commit if it's someone in Management Studio). Then the log will clear automatically.
Best Answer
Answer to your question is in This Blog by Paul Randal
Yep if the transaction has not committed when the full backup completes it will be rolled back when the backup is restored, simply, because SQL Server does not have idea about "state of transaction" when the backup finished before the transaction committed/completed. Please read below for more details
Understanding Backups in SQL Server
As stated above because it does not have any idea about the state of transaction, like whether it will commit or rollback and since transactions in SQL Server are first written in transaction log before changes are made to pages the backup so it does its job of backing up enough transaction logs so that if transaction commits it can bring database to consistent state after backup is restored and if transaction does not commits before backup is finished the backups takes it as uncommitted and when it will be restored their would be no information about changed made by that transaction in the restored database.
Read The First Myth pointed out by Paul Randal
Yes it does needs space in transaction log file to rollback. Rollback is mostly single threaded and undoes what the transaction has done.
Rollback: What happens when you kill a session