How to axe your transaction log

Posted At : September 5, 2008 5:36 PM | Posted By : Brad Wood
Related Categories: SQL, Security

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"):

1 BACKUP LOG foo WITH TRUNCATE_ONLY
2 DBCC SHRINKFILE(foo_log,2)

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.

Comments (2) |

Print |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 1521 Views

TweetBacks

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment] [Subscribe to Comments]

@Brad the transaction log is very important if there is ever a need to restore a database including the most recent transactions.

# Posted By Mike Brunt | 9/6/08 2:27 PM

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. :)

# Posted By Brad Wood | 9/6/08 2:33 PM

[Add Comment]