InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 03:27:29 GMT20http://www.sqlservercentral.com/Forums/Topic1388376-2852-1.aspxHi all, I borrowed the guts of what Timothy Parker did and created a version that works on one database only, so there is no outer cursor loop. I also added two output parameters, a try/catch, and renamed some object to suit my conventions.Thanks, TimothyJeff RoughgardenThu, 20 Dec 2012 14:33:11 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic1388376-2852-1.aspxHi everyone,Take a look at the attached file, which has the SQL script in known good formatted state. I'm guessing there may have been an issue with the code formatting in the article versus what I actually have in my SQL script.Please try the attached script and let me know if this resolves the issue.The attached script name is [b]ReorgRebuildIndex.txt[/b]. Of course you will need to change the extension back to a [b].SQL [/b]for it to run in SSMS.Tim P.Wed, 28 Nov 2012 08:37:26 GMTTim Parkerhttp://www.sqlservercentral.com/Forums/Topic1388376-2852-1.aspxTim,Executing the Stored Proc on 2008R2 gives this error....(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)Msg 102, Level 15, State 1, Line 11Incorrect syntax near '('.(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(0 row(s) affected)(1 row(s) affected)Tue, 27 Nov 2012 14:03:09 GMTcharlesafarrhttp://www.sqlservercentral.com/Forums/Topic1388376-2852-1.aspxI pulled the following code below from the script so I could try to debug my issue. You could use it to build the statements and then just run the ones you want.[code="sql"]Use YOURDATABASEGOSELECT avg_fragmentation_in_percent, CASE WHEN avg_fragmentation_in_percent BETWEEN 5 AND 30 THEN 'ALTER INDEX [' + name + '] ON ' + (SELECT TOP 1 TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = 'BASE TABLE') + '.[' + OBJECT_NAME(b.[OBJECT_ID]) + '] REORGANIZE ;' WHEN avg_fragmentation_in_percent &gt; 30 THEN 'ALTER INDEX [' + name + '] ON ' + (SELECT TOP 1 TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = 'BASE TABLE') + '.[' + OBJECT_NAME(b.[OBJECT_ID]) + '] REBUILD WITH (FILLFACTOR = 90) ;' END AS Index_Statement FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS aJOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_idWHERE avg_fragmentation_in_percent &gt; 5AND index_type_desc &lt;&gt; 'HEAP'AND page_count &gt; 640ORDER BY avg_fragmentation_in_percent DESC[/code]Mon, 26 Nov 2012 10:02:23 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic1388376-2852-1.aspxIs there a way to run this against only a single database within the SQL Server? When I ran it, it ran against all 12 databases being hosted by the instance.Thank you,Wes CrockettMon, 26 Nov 2012 08:57:29 GMTwesleycrocketthttp://www.sqlservercentral.com/Forums/Topic1388376-2852-1.aspxI'm running this but I'm getting[code="other"](0 row(s) affected)Msg 102, Level 15, State 1, Line 11Incorrect syntax near ')'.[/code]I'm trying to track it down but it's a little complicated, and it's still running so I don't want to mess with it until it's finished. Any ideas on what could cause this?Mon, 26 Nov 2012 08:43:32 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic1388376-2852-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/index+maintenance/94367/">Intelligent Index Reorganize and Rebuild Script - v1.0</A>[/B]Sun, 25 Nov 2012 01:59:17 GMTTim Parker