Maxrecursion issue SQL

I am trying to rebuild indexes from a list stored in a table. I am using IndexOptimize stored Procedure from https://ola.hallengren.com/ to accomplish. The difficulty is that it requires a table list and when I go over 100, I get a message from SQL Server regarding max recursion. I know that I can build a sp to do this but need a limit the time it takes to do which is built in. I was wondering If I coule get some help with max recursion error.


DECLARE @listStr VARCHAR(MAX)
DECLARE @myDatabase VARCHAR(55)
DECLARE @formatlist VARCHAR(MAX)
SET @MyDatabase = 'dbname'
SELECT @listStr =  + COALESCE(@listStr+',' , '')+ @myDatabase +'.'+ SchemaName +'.'+ TableName 
FROM IndexedTableList  WHERE id  >= 1 AND ID  < 102
SET @formatlist = @listStr
SELECT @formatlist


 
EXECUTE [master].[dbo].[IndexOptimize] 
@Databases = @MyDatabase, 
@LogToTable = 'Y', 
@FragmentationLow = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium = 'INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE', 
@MaxDop = 1,
@TimeLimit = 43200,
@Indexes = @formatlist

Open in new window

earngreenAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
Then I presume the issue is inside Ola's stored proc. Have you considered contacting him?

.
0
 
arnoldCommented:
Your issue deals with building the long list that exceeds..

There are scripts/sps that use a loop versus building the list and then looping based on the list.
0
 
earngreenAuthor Commented:
Please provide an example as I am not sure what you mean. I also tried For XML PATH ('')),1,1,'')  with no success. Do you mean loop through 100 at a time.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
arnoldCommented:
MS has a TSQl that looks at an index fragmentation and reorganizes/rebuilds based on a preset criteria.  The benefit of this it reduces the transactional database growth compared to using the
running the rebuild indexes on the entire DB without regard to its fragmentation.


 http://msdn.microsoft.com/en-us/library/ms188917.aspx
Look at Example D:
0
 
PortletPaulfreelancerCommented:
The default recursion of 100 is probably stopping this, so you could add your own maxrecursion hint

I just simulated this using a CTE so it may be different using a table (or you could use a CTE of the table)

DECLARE @listStr VARCHAR(MAX)
DECLARE @myDatabase VARCHAR(55)
DECLARE @formatlist VARCHAR(MAX)
SET @MyDatabase = 'dbname'

;with cte (id, SchemaName) as (
  select 1 as id, cast(1 as varchar) + ' a long string that follows after a number' as astring
  union all
  select id + 1 , cast(id as varchar) + ' a long string that follows after a number'
  from cte
  where id < 103
  )

SELECT @listStr =  + COALESCE(@listStr+',' , '')+ @myDatabase +'.'+ SchemaName --+'.'+ TableName 
FROM cte  WHERE id  >= 1 AND ID  < 102
OPTION (MAXRECURSION 200)

SET @formatlist = @listStr
SELECT @formatlist

Open in new window

BUT, take care that the wanted string isn't truncated!!!
0
 
earngreenAuthor Commented:
I think that generating the list is not the problem. I was able to put that in the stored procedure and run it. I am running this from SQL Agent by the way. The problem is when the list is generated and passed to the sp in @formatlist variable. I am getting the error. Otherwise when I execte the portion that gets the list alone there is no error.


EXECUTE [master].[dbo].[IndexOptimize] 
@Databases = @MyDatabase, 
@LogToTable = 'Y', 
@FragmentationLow = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium = 'INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE', 
@MaxDop = 1,
@TimeLimit = 43200,
@Indexes = @formatlist

Open in new window

0
 
earngreenAuthor Commented:
I dont think its the stored procedure as I can run this from ssms but not in a sql agent job.
0
 
PortletPaulfreelancerCommented:
so, it isn't in the piece immediately above the SP, and it's not in the SP.
? ...
afraid I am out of ideas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.