Post reply

Transaction Log back-up runs successfully but does not save to disk

  • August 17, 2004 at 5:58 am

    #360684

    I am after some assistance.

     

    I have a SQL 7.0 install running on a Windows NT server.

     

    Several days ago I was carrying out a database shrink via Enterprise Manager when I lost connection to the server. I carried out a DB restore to the back-up I had taken immediately prior to carrying out the shrink. All went well.

     

    My issue is with the DB Maintenance plan, specifically the transaction log back-up.  The daily back-u pis working fine and saving to disk. The transaction log back-up appears to be working. Checking the Jobs in Enterprise Manager I am told each run ended successfully (the back-up runs daily, every two hours, between 06:00 and 20:00). Hoewever the .TRN file is not being saved to the disk.

     

    I have checked the folder permissions, they are OK, I have checked the logs, there are no records on the transaction_log back-ups running. I have stopped and started all SQL services, I have recreated the DB maintenance Plan and  also  rebooted the server, all to no avail.

     

    Can anyone offer any guidance ?

     

    Thank you

  • August 18, 2004 at 2:14 am

    #519515

    Checked the database is not in SIMPLE recovery model mode?

    You only get transaction logs for backup if you are in FULL or BULK-LOGGED mode.

    Julian Kuiters
    juliankuiters.id.au

  • August 18, 2004 at 4:14 am

    #519532

    Julian, thanks for this. I had a eureka moment late last night and figured out the restore had set the database to a Simple recovery model mode.

    As you have probably guessed I am pretty new to SQL. I've trawled through the online books that came with SQL, they don't actually tell me how to convert a DB from Simple recovery mode to Ful, is it possible ?

    Rich

  • August 18, 2004 at 4:50 am

    #519535

    Yes. Basically you need to set the database options trunc. log on chkpt. and select into/bulkcopy to off/false. I think you can do this via the EM GUI (right click on the database, properties and then the options tab..?) - sorry, i've been using sql2k for years so i can't remember the specifics of sql7 EM.

    You can do the same thing via the sp_dboption stored procedure - check BOL for the details.

    d.

  • August 18, 2004 at 4:57 am

    #519540

    Thanks Douglas,

    I think I've nailed it. I'm runnig the

    select databasepropertyex('dbname','recovery')

    statement to clarify the recovery mode and then

    ALTER DATABASE command

     

    Thanks again for the response

    Rich

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

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