back up jobs, re-index question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • esource
    #1

    back up jobs, re-index question

    We have a db that is accessed by users on the web 24/7. Problem is
    when our maintenace job runs users are getting killed with this error:
    Database Error: SQL Error #-2147217900
    [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction manager has
    canceled the distributed transaction.
    (Source: Microsoft OLE DB Provider for ODBC Drivers)
    (SQL State: 37000)
    (NativeError: 1206)

    I'm assuming that one of the steps in the maintenance job causes this
    because it happens every night at the same time as the job
    Here are the steps in our job:
    1. differential backup
    2. kill users
    3. Alter database DBNAME set Recovery BULK_LOGGED
    4. reindex all tables
    5. Alter database DBNAME set Recovery FULL
    6. truncate log
    7. shrink log
    8. full backup
    9. resize database (if needed)

    Question is do we need to kill users before a re-index, re-size or
    backup occurs? if we don't will it just slow the process up? any
    other step that would cause that error?
    Ideally we need a different design for a web accessed solution but we
    need a quick fix now
    thanks in advance
    Tags: None
    • Greg D. Moore \(Strider\)
      #2
      Re: back up jobs, re-index question

      "esource" <darrenpaul@sha w.cawrote in message
      news:08dc130f-a3ce-44aa-ad62-0c894e423c5c@k1 3g2000hse.googl egroups.com...
      We have a db that is accessed by users on the web 24/7. Problem is
      when our maintenace job runs users are getting killed with this error:
      Database Error: SQL Error #-2147217900
      [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction manager has
      canceled the distributed transaction.
      (Source: Microsoft OLE DB Provider for ODBC Drivers)
      (SQL State: 37000)
      (NativeError: 1206)
      >
      I'm assuming that one of the steps in the maintenance job causes this
      because it happens every night at the same time as the job
      Here are the steps in our job:
      1. differential backup
      2. kill users
      This is not necessary.
      3. Alter database DBNAME set Recovery BULK_LOGGED
      4. reindex all tables
      5. Alter database DBNAME set Recovery FULL
      6. truncate log
      Why are you doing this step? If you really don't care about the log, set
      the DB to simple.
      7. shrink log
      Don't do this at all. It's detrimental.
      8. full backup
      9. resize database (if needed)
      Same thing, do't do this.

      http://www.karaszi.com/SQLServer/info_dont_shrink.asp
      >
      Question is do we need to kill users before a re-index, re-size or
      backup occurs? if we don't will it just slow the process up? any
      other step that would cause that error?
      It'll slow things down, but if you do it at night and intelligently, you
      should be fine.

      Ideally we need a different design for a web accessed solution but we
      need a quick fix now
      thanks in advance


      --
      Greg Moore
      SQL Server DBA Consulting Remote and Onsite available!
      Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html


        Comment

        • Hugo Kornelis
          #3
          Re: back up jobs, re-index question

          On Thu, 24 Apr 2008 07:53:25 -0700 (PDT), esource wrote:
          >We have a db that is accessed by users on the web 24/7. Problem is
          >when our maintenace job runs users are getting killed with this error:
          >Database Error: SQL Error #-2147217900
          >[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction manager has
          >canceled the distributed transaction.
          >(Source: Microsoft OLE DB Provider for ODBC Drivers)
          >(SQL State: 37000)
          >(NativeError : 1206)
          >
          >I'm assuming that one of the steps in the maintenance job causes this
          >because it happens every night at the same time as the job
          >Here are the steps in our job:
          >1. differential backup
          >2. kill users
          >3. Alter database DBNAME set Recovery BULK_LOGGED
          >4. reindex all tables
          >5. Alter database DBNAME set Recovery FULL
          >6. truncate log
          >7. shrink log
          >8. full backup
          >9. resize database (if needed)
          >
          >Question is do we need to kill users before a re-index, re-size or
          >backup occurs? if we don't will it just slow the process up? any
          >other step that would cause that error?
          >Ideally we need a different design for a web accessed solution but we
          >need a quick fix now
          >thanks in advance
          Hi esource,

          And here are some other comments that do not address your question:

          1: Why do you reindex all tables? Is just reorganizing or rebuilding the
          most fragmented indexes not sufficient in your case?

          2: Why do you shrink the log? It slows down performance later (as it has
          to grow the log again) and will cause your log file to become
          fragmented. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp.

          3: Same goes for "resizing" the database. Growing it before autogrow
          kicks in is a good thing; shrinking it is not. Especially not right
          after reindexing, since the shrinkprocess will cause your indexes to
          become fragmented again...

          --
          Hugo Kornelis, SQL Server MVP
          My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

            Comment

            Previous template Next
            Working...