The log history table script can be found in the download in the same file where the isp_ALTER_INDEX object is scripted. The log history table is stored in the same database as the isp_ALTER_INDEX database, otherwise the stored procedure will fail. Can you tell me where the log history is then stored? This is awesome and definately will help us out. I don't think I'd bother with OFFLINE index rebuilds though if ONLINE is available even if you have a time to do the work. If you want to override the stored procedure code, simply comment out and instead use PRINT You can make changes from there. The stored procedure provides the ability to do indexes ONLINE or OFFLINE depending upon the SQL Server edition as well as the index. Would you be able to help me on this request?
However, some of your apps are not 24/7 and we want to rebuild index OFFLINE instead. It seems the proc only provides rebuilding index ONLINE option. Re: Defragmenting/Rebuilding Indexes in SQL Server 20 You can download the new version of the stored procedure here. WHEN = 'NONCLUSTERED INDEX' AND = 'LOB_DATA' THEN 0 Here’s the code change in case you are interested: SET =ĬASE WHEN IN ( 'XML INDEX', 'PRIMARY XML INDEX') THEN 0 I don’t have any systems that use the XML data type, so it wasn’t until we had the SQL Server 2008 AdventureWorks database on a development system that we found the bug. In SQL Server 2008, the DMF was modified so that you could have either “XML INDEX” or “PRIMARY XML INDEX” for XML indexes in the index_type_desc column.
In SQL Server 2005, the sys.dm_db_index_physical_stats data management function outputted “XML INDEX” for the index_type_desc column when the index contained a column with the XML data type. The bug would only have been encountered on SQL Server 2008 systems that have XML indexes that need to be rebuilt based upon the input parameters. I have modified isp_ALTER_INDEX, which is the stored procedure that I wrote and use to defragment/rebuild indexes in SQL Server 2005 and SQL Server 2008.
Defragmenting/Rebuilding Indexes in SQL Server 20
0 kommentar(er)
