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

?

1 2 3 4 5 6

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

---