469,299 Members | 2,035 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

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
Jun 27 '08 #1
2 3041
"esource" <da********@shaw.cawrote in message
news:08**********************************@k13g2000 hse.googlegroups.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
Jun 27 '08 #2
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
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

25 posts views Thread by Digital Puer | last post: by
132 posts views Thread by Kevin | last post: by
182 posts views Thread by Jim Hubbard | last post: by
5 posts views Thread by =?Utf-8?B?RkxEYXZlTQ==?= | last post: by
16 posts views Thread by Mikhail Kovalev | last post: by
10 posts views Thread by Ranjana | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.