Monday, July 2, 2012

Quick! How do I truncate the transaction log?

Question: How do I truncate the transaction log without stopping the server or making a backup?

I just want to free up all the disk space it's using...

  • there's no MobiLink or SQL Remote replication involved,

  • I don't care about recovery,

  • I don't even care about the data,

  • but...
and it's a big but... I can't stop the server.

Answer: The dbbackup -xo parameter is what you want.

It tells dbbackup to do something out of the ordinary: just delete and restart the transaction log file, don't bother making a backup.

Think of it like telling the Queen to skip the crown and throne stuff today, we just need you to clean the toilets.

Here's the story in pictures...



  -c "ENG=inventory;DBN=inventory;UID=dba;PWD=sql"^
  -o c:\temp\dbbackup_log.txt^


and after...

Question from the audience: Why use a transaction log at all if you don't care about the recovery?

Answer: Because databases run faster with a transaction log. If there's no transaction log (dbinit -n), every COMMIT turns into a CHECKPOINT.

Question: Why use a database at all if you don't care about the data?

Answer:: Gosh, maybe you should get out more... Databases are used for many purposes, including high-throughput applications where it's far easier to re-read or recreate the data from scratch, or just start over, than to use formal database backup and recovery techniques.

It's like saying "Never use SELECT * !" because people get burned when they use it inappropriately... dbbackup -xo is the same, if you use it when you shouldn't, life will be hard.


John Smirnios said...

In SQL, you can execute


Justin Willey said...

The Queen was in Scotland last week and mentioned how she had enjoyed reading Breck's metaphor about log backups and said she had a little surprise for him in return: Surprise for Breck

A Pedant said...

Opps, for metaphor read simile! That's another for the chop (offence: misuse of the Queen's English).

Breck Carter said...

@Pedant: Have things changed in Scotland? You now bow and scrape before the English Queen? :)

Ex Pedant said...

I thought she was all yours! The Queen of Canada

... but you're behind the times - even the SNP (Scottish National Party) has gone all monarchist these days. Monarchism - the new republicanism!

Anonymous said...

Five years later and still helpful. Thank you so much!