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