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

Before...


during...

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=inventory;DBN=inventory;UID=dba;PWD=sql"^
  -o c:\temp\dbbackup_log.txt^
  -xo

PAUSE

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 innappropriately... dbbackup -xo is the same, if you use it when you shouldn't, life will be hard.

Dilbert.com



6 comments:

John Smirnios said...

In SQL, you can execute

BACKUP DATABASE DIRECTORY ''
TRANSACTION LOG ONLY
TRANSACTION LOG TRUNCATE;

http://dcx.sybase.com/index.html#1201/en/dbreference/backup-statement.html*d5e34394

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!