0

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.

0

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.

0

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?

If you haven't performed any transaction log backups since the incident where some data failed to be inserted, and the database has been using the FULL recovery model the whole time, then the data from the failed insert should be in the transaction log. Based on the size of your transaction log, it seems like this is likely the situation.

You can access the transaction log via the sys.fn_dblog system table-valued function, as well as some other even more arcane formats, but none of them are particularly human readable.

If this data is really important, you may consider using a commercial transaction log reading tool like ApexSQL Log. It looks like they have a free trial, so you could give it a try and see if it helps. I haven't used the tool, nor am I affiliated with it, I just thought it was worth pointing out that there are tools like this out there for situations like yours.

I think that Quest Litespeed has features around log reading and object level recovery as well.

Good luck!

1
  • 1
    Your first paragraph is the exact situation. Unfortunately, it's been decided that the hours it'll take to recover exceed the value, so I will probably never know, but the input was appreciated. – tmband25 Feb 1 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.