4

We have a big transaction log (1.3 GB) for a relatively simple SQL Server 2008 database (30 MB). It (log) contains all updates since first time when db was put into production and (now we see it) represents a valuable source of temporal data which would be interesting to us.

There would be some way to "replay" this entire log on a similar db (like the original, but with history tables and triggers added)?

This way we could reconstruct the same db, but with temporal data "extracted" from logs. This is a valuable knowledge that we overlooked the first time and should not rest on server log files.

UPDATE

I am NOT having any problems with "large" transaction logs. I DO NOT want to truncate the log. The temporal information which is contained in it is valuable (I sincerely expect that it would be clear now, since this is the THIRD time I repeat it).

To the "fastest gunners of west" out there, please do continue reading after "We have a big transaction log ..." above. I'm starting to think that actually was MY FAULT to begin a question with these words, since it appears that 80% of readers think that the question is about log truncation.

And to anyone who may wish to "suggest" another backup-and-log-truncation as a "solution" (BTW, completely missing the point), please read this.

Improve this question
2

5 Answers 5

Reset to default
5

Transaction logs contain the binary deltas of modifications applied to a database since the last log truncation; the keyword here is "binary": they don't contain SQL queries or something similar, but are actually more akin to binary patches to be applied to a program.

For this reason, they can be replayed only on the exact physical database files they were originally linked to; replaying them on another database (even with the same schema) would be exactly like applying a patch to a different executable to the one it was written for: not possible at all.

You also can't replay them on the same database, if that gets modified; i.e., you can't restore the database from an old backup, bring it online, make any modification at all to it and then replay the logs against it; you actually lose any log-replaying capability as soon as you bring the database fully online (there's even a specific flag in SQL Server restore operations for that).

Improve this answer
3

Here they say:

Note that you can dump the contents of the current log file using an undocumented DBCC command:

DBCC LOG('<dbname>', [<option>])

where <option> is an integer between 1 and 4, which controls the level of detail of the information displayed by DBCC LOG.

If i do this with one of my db's I get a lot of information, but i think not enough to replay the log.

In other posts they mentioned these products which may solve your problems:

Improve this answer
2

After re-reading your posting a few times.. I'd say you should look for some thirdparty log analysis tools.

If you have Sql Server 2000, the RedGate SQL Log Rescue tool is free. I haven't looked for other tools, but I'm sure there are some.

Improve this answer
2
  • Unfortunately, SQL Log Rescue only works for MSSQL 2000. Red Gate made the decision not to support newer versions :( You may have to look at tools from ApexSQL for SQL 2008
    – Joe
    Jun 30, 2011 at 16:55
  • Has anyone found a capable tool yet? I'd like to try the same idea on a backup to see what happens after turning snapshot isolation on and using it in a few frequently called stored procedures. Re-running a day's worth of log would be greatly helpful to get some insight. It would be even better if the tool would simulate a degree of parallel transactions so we could do A/B testing on how deadlocks work out. Off course the transaction logs don't have all the read-only stuff that's going on, but they would solve at least part of the simulation needs.
    – Louis Somers
    Sep 6, 2019 at 9:33
0

Go buy third party software to get the transactions, since RESTORE LOG is apparently not compatible with your changes (as originally suggested).

Also, you should have use SQL Server Profiler if you really wanted to rerun a bunch of transactions. However, you've poorly planned this, so you'll be out of pocket.

Improve this answer
4
  • @Cyber: Keep in mind that (a) you're a new user and (b) I have no idea what your proficiency with SQL is. Therefore, your cantankerousness toward both John and I is unwarranted. If this doesn't help you, I'll delete my post, but I will not help you any more, now or in the future.
    – Eric
    Jul 5, 2009 at 17:31
  • Excuse me, Eric, but you really should give more importance to what someone asks than to his "reputation". I didn't post this question here to gain personal benefits or help, but to possibly attract people who also had the same problem and to share experiences. This is (IMO) the entire concept behind StackOverflow. If you don't want to 'help me', ok. But if you already had some situation like this and want to share something useful, the community will be grateful.
    – Cyber
    Jul 5, 2009 at 17:53
  • BTW: thanks for the answer! I'm giving a look now on these tools. You are absolutely right about our lack of planning about this problem, and now we have to pay the price (literally, maybe :) )
    – Cyber
    Jul 5, 2009 at 17:56
  • Cyber, I will find it interesting to see whether the experts on serverfault consider it wise to never truncate your log. It was my understanding that it was not wise, so I treated you as someone who had made a mistake which, on the surface, seemed to be a novice error. It seemed you were then trying to benefit from the fact that you had unwisely failed to ever truncate the log. Personally, I tried to help you with the "unwise" part.
    – John Saunders
    Jul 5, 2009 at 21:31
0

Question Rewritten after Clarification

I voted to move this to serverfault.com. They need to tell you that you misread Backup Log with Truncate_Only: Like a Bear Trap. Note the part about "with truncate only".

See Shrinking Databases, which is linked to the first article, read it and understand. It's not that you should never truncate your logs: it's that you should only truncate them in conjunction with a proper schedule of full backups, transaction log backups, and possibly differential backups.

Once the log has been securely backed up, it is no longer necessary to keep it around on disk. However, I'm not a DBA, and I think you may need to listen to the experts in order to understand this, so I'm voting to move.

Improve this answer
4
  • Yes. DB is in full recovery. Yes, it has a 1.3 ldf file. And thanks, but we know how to truncate a transaction log. Actually, we also know why this is a stupid thing to do if you value your data and have TBs of cheap disk space today. Please re-read the question to understand WHAT we are trying to do.
    – Cyber
    Jul 5, 2009 at 16:32
  • Are you backing it up? And I suggest you edit your question to show you're doing this deliberately. Your question sounds like it's inadvertent.
    – John Saunders
    Jul 5, 2009 at 16:37
  • Ok, I re-read that. I think your question should be "How to restore a transaction log to a new database with a different schema", and should be on serverfault, since it's not about programming.
    – John Saunders
    Jul 5, 2009 at 16:39
  • Thank you. Your suggestions were very useful. Including the one that recommend to rewrite the title to the exactly same thing but in another words.
    – Cyber
    Jul 5, 2009 at 17:00

You must log in to answer this question.