Friday, December 15, 2006

SQL server transaction log

I have been having a problem with an old SQL 2000 database, the transaction log was about 7Gb and the data file around 36mb.
Obviously this is a rather large file to be moving around on a flash drive.

Normally I would back up both transaction log and main data file individually, and run a shrink database command on each file.
Unfortunately this would only remove a few mb at a time.

After hunting around and debating whether to import all of the tables into a new database I found this

And its really simple, it will not work on a 'live' running database as there can be no connections (so a website scenario wont work well) before you take the database offline and delete (yes thats delete) the transaction log file.

When you re-attach the database it creates a new (clean) transaction log.

DO NOT try this if you are unsure of how a SQL server runs and always remember to back up your database and all log files before undertaking any tasks that could cause problem.

I know from experience that SQL server can be very unforgiving, what 'No CTRL-Z' for undo I hear you say....

No comments: