back up jobs, re-index question

[esource]

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

[Greg D. Moore \(Strider\)]

"esource" <da********@sha w.cawrote in message
news:08******** *************** ***********@k13 g2000hse.google groups.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

[Hugo Kornelis]

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