I have a SQL Server Express database with Full Recovery Model. Several months ago, the database hit it's size limit (10GB) and some data did not successfully get added to the database. Several weeks after that point, the database began receiving monthly full backups, but never any transaction log backups. The transaction log is still an overly sized file (>200GB). Both files are free to grow without limit.
Is it possible that this transaction log will contain the data that failed to be inserted several months ago, or is the file just that big because of no truncation/shrinking?
I appreciate that monthly backups don't make any sense with a Full Recovery Model. That was just the as-found condition.
Best Answer
The size of the Transaction Log is just due to its normal growth pattern based on the amount of data that's been transacted between backups. It'll grow to whatever size it needs to hold transactions until a backup is taken on the log which flushes those hardened transactions BUT does not shrink the log file. Rather that space remains as reserved empty space that the Transaction Log will re-use for new transactions.
So there is no correlation between the log file's size and if your failed to insert data is in it. At best it may be in one of the previous backups of it, as a rolled back transaction, and in a non-human readable form. Unfortunately that failed data insert is likely gone, at least in the context of this question.