How to axe your transaction log
Posted by
Brad Wood
Sep 06, 2008 00:36:00 UTC
If you are using MS SQL Server and ever want to just obliterate your transaction log, you can use the following SQL (where your database name is "foo"):
[code]BACKUP LOG foo WITH TRUNCATE_ONLY DBCC SHRINKFILE(foo_log,2) [/code]Don't ever do this to a database you care about like, say, production. I wanted this because I am screwing around creating rainbow tables of SHA-1 hashes. The Cartesian product of joining a table to itself on 1=1 is very handy for producing all possible combinations of a set of characters. Inserting a few million records can put a lot of crap in your transaction log though.
Mike Brunt
@Brad the transaction log is very important if there is ever a need to restore a database including the most recent transactions.
Brad Wood
Yes it is.
That's why I threw in the bit about never doing it to a database you cared about. Like I said in the post, my example was just a junk database I was using to create rainbow tables. I was inserting millions and millions of records which was making my transaction log huge. Since it was just an experiment on my local box, I wouldn't have cared if the whole database fell off a cliff. Hence, the cold-hearted axing that commenced. :)