Post reply

Dump Tran for all DBs

  • January 14, 2011 at 2:26 pm

    #149658

    Hello,

    How can I dump tran for all DBs, I have the following but it gives me error:

    exec sp_msforeachdb ' use[?];

    dump tran db_name() with truncate_only'

    Thanks.

  • January 14, 2011 at 2:39 pm

    #1273811

    I found the solution:

    exec sp_msforeachdb ' use[?];

    DECLARE @DBName VarChar(150);

    SET @DBNAME = db_name();

    DUMP TRAN @DBNAME WITH TRUNCATE_ONLY'

  • January 14, 2011 at 3:02 pm

    #1273821

    Why are you truncating the transaction logs? Do you know what that's going to do to your log chain and your ability to restore to point-in-time?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 7:04 am

    #1274231

    The logs are getting full every so often and we do not know why. One of the reasons could be that we are running profiler from time to time.

    Thanks.

  • January 17, 2011 at 7:19 am

    #1274236

    Profiler won't cause a transaction log to grow.

    You need to investigate the matter, not just throw away the ability to recover your database. Start by querying sys.databases and looking at the log_reuse_wait_desc for the database. If it's log backup then check that your log backups are running correctly.

    If you don't need the ability to restore to a point in time, if restoring to the last full backup and losing all data since then is acceptable in the case of a disaster, switch the database to simple recovery model.

    Please read through this: http://www.sqlservercentral.com/articles/64582/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 17, 2011 at 8:45 am

    #1274271

    Thanks Gail. That was really helpful.

  • January 17, 2011 at 9:03 am

    #1274278

    note that DUMP is an old keyword and likely to be removed at some point. BACKUP TRANSACTION is preferred

    http://msdn.microsoft.com/en-us/library/ms186865%28v=SQL.100%29.aspx

  • January 17, 2011 at 9:31 am

    #1274297

    Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply