February 15, 2010 at 12:41 am
Hi,
We have SQL server 2005 EE x64 with SP3 having BizTalk server 2006 R2 databases. I went through the below links, before performing index rebuild for BizTalk databases.
http://msdn.microsoft.com/en-us/library/cc296811%28BTS.10%29.aspx
http://support.microsoft.com/kb/917845
As per the above links, we CANNOT reorganize indexes for BizTalk databases and BizTalk supports only Index rebuilds:
1. When NO BizTalk data processing is running
2. We must use only the BizTalk provided stored procedures to rebuild the indexes
and I read MSDN forums that we should
3. Stop the SQL Agent and run the Index rebuild stored procedures (bts_RebuildIndexes, dtasp_RebuildIndexes )
Questions:
Is that true that we need to STOP SQL Agent before running the above Stored procedures?
Did you stop the BizTalk data processing every time you rebuild indexes?
I appreciate your inputs
Thanks
February 16, 2010 at 4:58 am
Sometimes the left hand at Microsoft does not talk to the right hand. This can result in experts in one product saying how to use another product they actually know very little about.
Rebuilding indexes is a standard SQL Server operation, and can be run in parallel with application workloads. However, you need to be aware of what the workload is, because some such as BizTalk may experience contention while the index rebuild is run.
Also with just about all applications, it is not good practice to run a single DBCC DBREINDEX to rebuild all indexes in a database in one operation. It is best practice to only rebuild the indexes that need rebuilding. Selective rebuild saves time and disk space and reduces potential contention.
It is likely there are many tables in the BizTalk databases that can be rebuilt without impacting other active work within BizTalk. Working out which tables they are may not be easy - but trial and error will give you a list to work with.
The advice about stopping SQL Agent during a index rebuild is probably because Agent runs scheduled processes that use the BizTalk database. Because Microsoft have not done the work to identify what tables can safely have an index rebuild done on them while other BizTalk work is running, they are playing safe and telling you to stop all BizTalk work.
Telling customers to stop using a product while SQL Server maintenance is running does nothing to improve perception of both the product or of SQL Server. IMHO this is a lazy approach. Customers that rely on BizTalk (also SharePoint which has similar issues) being available 24x7 should pressure Microsoft into giving better advice.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply