back up jobs, re-index question
am 24.04.2008 16:53:25 von esourceWe 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