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.)
Here is the answer to my own question.
Run the below query to get information about the log file's reuse wait:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'
I got the following output:
log_reuse_wait_desc
-------------------
REPLICATION
There were some replication-related objects remaining in the database, even after removing the replication.
To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.
The solution was to import the database contents to another database using the import option of SQL Server.
Best Answer
I think there may be some confusion here on the word "Shrinking" - The log file is not ever "Shrunk" by a backup. It can be "truncated" by the log backup, but not shrunk.
When a log is truncated, the segments in the log that are no longer needed for recovery (either because you are in SIMPLE recovery model and a checkpoint has ensured that the data is in the data file securely, or you are in FULL recovery model and the log records have been backed up with a transaction log backup) are effectively marked for overwrite. The log is allowed to use the space those "old/unneeded" log records consumed because they aren't required for recovery.
Note I didn't say "Shrink". The truncation means you can possibly continue writing to the space already allocated to the log file - if there is enough. But truncation will NEVER shrink a log file. It just means it may not have to grow more.
Truncation is more or less an internal log file maintenance type operation. I love what they used to call SIMPLE recovery model back in SQL Server 2000 - "Truncate on checkpoint" mode.
Shrinking is a physical operation on the file itself. I have a few opinions on shrinking data and log files in a rather long answer here. But, in general, don't fear a bigger file. Fear an out of control file that is growing unconstrained. If you are truly taking log file backups, and SQL is showing that you have - you likely just have a file with mostly empty space - meaning the file can be rewritten, and will. It's rather healthy to have a file that is "right-sized" to not have to shrink/grow and shrink/grow between log backups.