Post reply

Transaction logs

  • January 14, 2009 at 7:21 am

    #129462

    I need to start keeping data every 4 hours for transaction logs. DB is saet to full, how can I do it?What is the code to run? I know how to set up a job but don't know what code to run Thank you

  • January 14, 2009 at 7:34 am

    #926562

    quickest and easiest way to do this is to create a maintenance plan via SSMS. Use transaction type from the backup task.

    ---------------------------------------------------------------------

  • January 14, 2009 at 8:01 am

    #926577

    Look up BACKUP LOG in Books Online.

    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 14, 2009 at 8:16 am

    #926593

    I sat up a maintance plan and when I ran I get an error:

    This is my subplan:

    BACKUP LOG [Prod_Efficiency] TO DISK = N''F:\\Prod_Efficiency_backup_200901140944.trn'' WITH NOFORMAT, NOINIT, NAME = N''Prod_Efficiency_backup_20090114094452'', SKIP, REWIND, NOUNLOAD, STATS = 10

    Hereis an error:

    Executing the query "BACKUP LOG [Prod_Efficiency] TO DISK = N'F:\\\\Prod_Efficiency_backup_200901140944.trn' WITH NOFORMAT, NOINIT, NAME = N'Prod_Efficiency_backup_20090114094452', SKIP, REWIND, NOUNLOAD, STATS = 10

    " failed with the following error: "Cannot open backup device 'F:\\\\Prod_Efficiency_backup_200901140944.trn'. Operating system error 5(Access is denied.).

    BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Error:-1073548784

    What I am doing wrong.

    Thank you

  • January 14, 2009 at 8:22 am

    #926599

    Why a double \\ in the file name?

    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 15, 2009 at 8:07 am

    #927346

    Another way is to set up a single job, with a schedule to kick it off every four hours, and run the BACKUP LOG command from within the job. Maybe add an alert, or a step to email you, in case the job fails.

    The code to create a backup is found in BOL (SQL Server's help file, aka Books On Line), under BACKUP.

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

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