Post reply

Dbase Size Growing

  • March 29, 2011 at 4:04 am

    #237547

    HI all,

    I've a Dbase which update table everyday via running store procedure, problem is Trans log size grow about 500MB everyday, because of running following store procedure.

    EXEC sp_dboption serviceDeskDB, 'select into/bulkcopy', 'true'

    EXEC usp_truncate_all_Calls_analysis_table

    EXEC usp_populate_all_Calls_analysis_table

    EXEC usp_Update1_all_calls_analysis_table

    EXEC usp_Update2_all_calls_analysis_table

    EXEC sp_dboption serviceDeskDB, 'select into/bulkcopy', 'false'

    I need your expertise how to reduce Trans log for above procedure......

  • March 29, 2011 at 4:28 am

    #1304750

    setup regular t-log backups.

  • March 29, 2011 at 4:42 am

    #1304753

    Don't use the select into db option, it's been deprecated since SQL 2000. The replacement is to set the recovery model, in this case to bulk-logged recovery and back to full.

    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

  • March 29, 2011 at 4:55 am

    #1304755

    Thanks Gail,

    but I'm using SQL 2000, is there any other option using this procedure not to increase T-log?

  • March 29, 2011 at 5:07 am

    #1304761

    There was nothing in my post that's 2005 or above only.

    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

  • March 30, 2011 at 9:20 am

    #1305501

    steveb. (3/29/2011)

    setup regular t-log backups.

    There's your answer .... Every 15-20 minutes for us.

Viewing 6 posts - 1 through 6 (of 6 total)

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