back up jobs, re-index question
in [Programming SQL]
|
Prev: The TRUNCATE TABLE SQL construct or statement is not supported
Next: Creating parameterized query in SSIS package
From: esource on 24 Apr 2008 10:53 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
From: Alejandro Mesa on 24 Apr 2008 11:37 Sorry for not giving you the answer you are looking for. I wonder why do you have the db in FULL recovery mode if you are not doing any log backup? AMB "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 >
From: Greg D. Moore (Strider) on 24 Apr 2008 19:18 "esource" <darrenpaul(a)shaw.ca> wrote in message news:08dc130f-a3ce-44aa-ad62-0c894e423c5c(a)k13g2000hse.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
From: Hugo Kornelis on 24 Apr 2008 19:16 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
From: Ola Hallengren on 30 Apr 2008 17:50
>Why do you reindex all tables? Is just reorganizing or rebuilding the most fragmented indexes not sufficient in your case? If you would like to do the index optimization like this, I have a stored procedure that you are welcome to use. You can save a lot of server resources and backup log space, by not rebuilding all indexes. http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html Ola Hallengren http://ola.hallengren.com "Greg D. Moore (Strider)" wrote: > "esource" <darrenpaul(a)shaw.ca> wrote in message > news:08dc130f-a3ce-44aa-ad62-0c894e423c5c(a)k13g2000hse.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 > > > |