Post reply

how to get my backup to dump the transaction file?

  • June 2, 2004 at 9:59 pm

    #108450

    hello,

    im a novice with 3 sql servers at work. how do i get my backup maintaince plan to dump the transaction file once backup is complete?

    or how to i jsut setup a job to dump the backup file?

    my transaction log file is 19GB on my live database! help!

    thanks!

  • June 3, 2004 at 7:28 am

    #508830

    Do regular log backups.

    -SQLBill

  • June 3, 2004 at 6:11 pm

    #508918

    hi sqlbill,

    can you tell me exactly how to do this?

    thanks

  • June 4, 2004 at 12:37 am

    #508938

    Andrew,

    Look in Books Online under 'Backups' and 'Maintenance Plans'. 

    To back up the transaction log as part of a maintenance plan open the maintenance plan and click on the transaction log backup tab and make the appropriate settings.  You will probably need to shrink the log after you have backed it up otherwise it will still consume 19GB.

    To shrink the log run:

    DBCC SHRINKFILE(yourlogfilenamehere, TRUNCATEONLY)             

    If you don't need to keep backups of the transaction log, I suggest you change your recovery mode to bulk-logged or simple and run:

    BACKUP LOG yourdatabasenamehere

    WITH NO_LOG

    Cheers,

    Angela

  • June 4, 2004 at 9:47 am

    #509045

    Setting the option to simple or bulked is fine if you don't do transactional backups with Veritas; however, if you do, then you will get error messages in your backup job.

    Is there a way to automate the DBCC SHRINKFILE command to run on a scheduled basis?  Can a job be created to do this?

    BillH

  • June 4, 2004 at 10:38 am

    #509056

    Yes, you can create a job to do this. But if you want it done regularly and don't care when it's done, set the database to AUTO SHRINK. (Enterprise Manager, right click on the database, select Properties, go to Options tab).

    -SQLBill

  • June 4, 2004 at 2:12 pm

    #509088

    Beware the "auto shrink" option may cause performance issues.  Best to create a job to dbcc shrinkfile with the recovery model in "simple" if tranlogs aren't required.

    Good discussion of the "auto shrink" issue here:

    http://www.sqlservercentral.com/columnists/mpearson/autocloseandautoshrinkjustdont.asp

     

    Regards, Melissa

  • June 6, 2004 at 2:13 pm

    #509160

    Don't use autoshrink.  It sounds like you are not doing transaction log backups so set the recovery mode to simple.  Also, don't use Veritas to backup the databases if you are (that wasn't your comment).  Just use SQL Server backups.

    After you set the recovery mode to simple, you need to go to Query Analyzer and run this for each db.

    CHECKPOINT

    GO

    DBCC SHRINKDB('databasename')

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • June 8, 2004 at 7:29 am

    #509465

    Thanks Derrick, however, here is my current backup schedule:

    Microsoft SQL Server Full Backup:  Every Morning at 6:00 a.m. (takes about 8 minutes for all databases on each server.

    Microsoft SQL Server Transaction Log Backup:  Every two hours.

    Veritas SQL Server - 10:30 p.m. - full backup to disk

    Veritas SQL Server Daily - 1:00 a.m. - transaction backup to tape

    Veritas SQL Server Full - 7:00 p.m. saturdays - full to tape

     

    If I set my recovery mode to simple, then I get an error during backup on both SQL Server and Veritas.

    Is there a way to change the db to simple, then dbcc shrinkfile (filename) (as it is my transaction logs that seem to grow at 5 - 10 times the db size on certain db's), the change the db back to full?  Am I over-thinking this or am I missing something?

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

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