Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Best Recovery model

    What would be the best Recovery Model for: a database which is 4 gig in size and imports via MSAccess queries and also stored procedures approximately 400,000 meg of data each month (and some other update queries are run against it) and it is also queried off of for totals on weekly basis?

    The problem is that the SQL Server box only has 512 meg of memory and the tranlog on this database grows tremendously each import and when update queries are run against it. This tends to slow things down a bit on our other databases. We are getting a new SQL Server box but until then, what would be the best recovery model? I currently have it as Bulk-Logged and allow the tranlog to grow by 10% (with a base of 250 meg). The tranlog grows to up to 5-10 gig and in order to shrink it, I have to change the recovery model to Simple, and then back to Bulk-Logged in order to shrink it (I've tried all the dbcc shrinkdatabase, dbcc shrinkfile, dbcc showcontig, and dbcc checkdb commands as well as BACKUP LOG dbName WITH TRUNCACTE_ONLY and nothing will shrink it unless I change the recovery model to simple.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps you could turn off transaction logging during bulk data loads.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Bulk-Logged

    Thanks for the repy blindman,

    I tried that and it slowed down the importing to a crawl (1-2 hours verses 15 minutes). I know I'm pushing the limits on the SQL Server box.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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