Link to home
Start Free TrialLog in
Avatar of dakota5
dakota5Flag for United States of America

asked on

DUMP TRANSACTION command for SQL Server 2008

I have a line of code from SQLServer 2005

DUMP TRANSACTION dbname WITH TRUNCATE_ONLY

DUMP command was eliminated in SQL2008.  The appropriate statement would be (I think)

BACKUP LOG dbname

But what is the equivalent option for TRUNCATE_ONLY (which is not accepted in SQL2008)

NO_LOG?   or COPY_ONLY, or NO_TRUNCATE?   Something else?

Thanks in advance.

Avatar of Lara F
Lara F
Flag of United States of America image

there is no TRUNCATE_ONLY any more in SQL 2008 - it is considered "bad practice"

If you routinely do DUMP TRANSACTION dbname WITH TRUNCATE_ONLY
You better switch your DB to simple recovery mode and you will no longer need to worry about  log.
Avatar of dakota5

ASKER

OK.  I See.  Excellent explanation.

The issue is that the database needs to run in a full recovery model; this obsoleted command (DUMP TRANSACTION dbname WITH TRUNCATE_ONLY) is in the middle of a utility script that we run every month or so (that the vendor wrote).  The database runs 24/7 and we really can't (or shouldn't) change it to simple recovery mode.

Are there any way (and is it safe) to  on the fly
1. change to simple recovery mode
2. run the utility
3. change back to full recovery

Or safer, just include in the utility code a TLOG backup-- that will shrink the transaction log.  How would I do this inside a procedure?
ASKER CERTIFIED SOLUTION
Avatar of Lara F
Lara F
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dakota5

ASKER

This answer was outstanding;  appreciate the links to other references.