Legitimate uses of DUMP TRAN

Question

I recently had to root out the cause of intermittent ODBC errors in a 12 year old SQL Based application. The culprit turned out to be this statement at the end of one of the stored procedures:

DUMP TRAN NotTheRealDBName WITH NO_LOG

I wasn't familiar with this command, so I did some research and found that it makes a backup of the transaction log. I believe this would only be useful if you at some point restored the transaction log, which didn't seem to be happening. The stored procedure in question was a simple sequence of inserts, updates, selects, and deletes on various tables.

I'm trying to imagine why one would want to backup and restore the transaction log in the middle of business logic, but I can't get my mind around it. Can anyone give me an example of an efficacious use of DUMP TRAN?

Answer 1

This command is used when you want to shrink your database; I never saw it used in business logic and I believe it to be actually dangerous when used that way.

Answer 2

They key part is that the program also adds the WITH NO_LOG clause. So basically it truncates the log. Probably 12 years ago the author had run into log growth issues, 'thoroughly' researched the issue and concluded that the best avenue is to truncate the log whenever he was doing some lengthy updates. Nevermind that in the process he breaks the backup chain of any maintenance plan...

Answer 3

DUMP is now BACKUP and I'm pretty sure the NO_LOG option has been discontinued in SQL Server 2008 (and with good cause).

Basically, if you configured your database improperly - if you used the "Full" recovery model but didn't actually do proper backups - then this was a way to shrink the log.

I can think of very few good reasons why you'd want to truncate the log without backing it up, and I also can't think of many reasons why you'd want to back up the log in the middle of business logic, but there is a very good reason to backup the transaction log in general, and that is to meet RPO targets.

Generally you're only going to be doing one database backup per day, but if you have a business requirement to be able to recover half a day's worth of transactions if the server blows up or some rogue DBA manages to hose your production database and all mirrors, then you'll likely be making hourly (or more frequent) transaction log backups. That way, you can restore the backup from yesterday night and use the transaction log backups to restore up to an hour ago (or whatever the RPO is).