Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: transaction log backups

    I have 5 databases on my sql server and they are taking backups on everyday twice. ANd we are not taking any transactional log backups.Now i have very larger log files which are more than 6 GB each.And the databases are less in size compared to the logfiles.
    Should I take transaction log backups with truncate option...
    PLease suggest me the best stratagy i should follow to overcome the problem.
    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Just set database model to simple...

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    where and how can i do that?

  4. #4
    Join Date
    Mar 2004
    Posts
    8
    In Enterprise Manager, right-click on your database and choose Properties. Then go to the Options tab. Under the "Recovery" category you'll see a drop-down list of recovery models. Simple is one of the choices here. Changing the setting is a "safe" thing to do even while there are users connected to the database.

  5. #5
    Join Date
    Apr 2003
    Posts
    176
    I didn't find any tab called options there....

  6. #6
    Join Date
    Mar 2004
    Posts
    8
    What version of SQL Server are you running?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    alter database your_db_name set recovery simple
    go

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    If you're using SQL version 7 then look for 'Trunc.log at chkpt.' which truncates the log at the chekpoint, from database properties goto Options or from query analyzer run SP_DBOPTION. Books online has got more information in this regard.

    Follow as specifeid by David if you're using SQL 2000.
    And still you can use SP_DBOPTION from query analyzer, and books online again for help.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SP_DBOPTION does not affect Recovery Mode in SQL2K.

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Thanks for the correction, still you can use Trunc.log at chkpt.
    And in this case ALTER DATABASE is best used as referred by BOL :The database options can be set by using the SET clause of the ALTER DATABASE statement, the sp_dboption system stored procedure or, in some cases, SQL Server Enterprise Manager.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Satya,

    At least open QA yourself and try it. In SQL2K you can SP_DBOPTION all day, but until you use "ALTER DATABASE", - your recovery mode will stay unchanged.

  12. #12
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    My reply is intended to change RECOVERY MODEL you can use ALTER DATABASE or EM.

    But using SP_DBOPTION you can change 'trunc. log at chkpt.' setting.
    Trunc.log at chkpt is similar to SIMPLE recovery model in SQL 2000.

    I feel I'm comfortable with my statements, though I admitted above.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  13. #13
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i'm on the fence about the whole thing....

  14. #14
    Join Date
    Sep 2003
    Posts
    522
    actually, sp_dboption 'db_name', 'trunc.', true/false does affect the recovery mode. run a test and you'll see.

  15. #15
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    one more thing can be done, if u need any point of time recovery put your databae in full recovery model and create a job that will take transaction log backup with truncate option after every two hours during the working hours.

    this way your log file size will remain under control with best recovery option.
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •