usp_AdaptiveIndexDefrag - Error 911 being reported when debug mode = 1

[d-moloney]

Declare @ErrorNo Integer EXECUTE @ErrorNo=[dbo].[usp_AdaptiveIndexDefrag] @timelimit=720, @outputResults =1, @debugMode=1,@forceRescan=1 print @ErrorNo if (@ErrorNo <0 ) RaisError('Error', @ErrorNo,1);

. . .

Determining modification row counter for statistic [PK_tblrptRptUser] on table or view [tblrptRptUser] of DB [LTAArchiveDB]... Using sys.dm_db_stats_properties DMF... Error 911 has occurred while determining row modification counter. Message: Database '[LTAArchiveDB]' does not exist. Make sure that the name is entered correctly. (Line Number: 1)

 No need to update statistic [PK_tblrptRptUser] on DB [LTAArchiveDB] and object [tblrptRptUser]...

Why is the message being outputted with an error?

[Adedba]

ran into the same issue today too. After looking into the code I can now see why because the variable @dbname is periodically being assigned its value from respective tbl_Adaptive tables e.g:

SELECT TOP 1 @objectName = objectName, @schemaName = schemaName, @indexName = indexName, @dbName = dbName, @fragmentation = fragmentation, @partitionNumber = NULL, @pageCount = page_count, @range_scan_count = range_scan_count, @is_primary_key = is_primary_key, @fill_factor = fill_factor, @record_count = record_count, @ixtype = [type], @is_disabled = is_disabled, @is_padded = is_padded, @has_filter = has_filter, @currCompression = [compression_type]
                FROM dbo.tbl_AdaptiveIndexDefrag_Working
                WHERE objectID = @objectID AND indexID = @indexID AND dbID = @dbID AND ((@Exec_Print = 1 AND defragDate IS NULL) OR (@Exec_Print = 0 AND defragDate IS NULL AND printStatus = 0));

Dbname in these tables is stored quoted with square brackets so during the subsequent statement assignments when Quotename is being applied it is being double quoted and SQL cannot recognize the DB name.

Example of the double quoting code:

SELECT @rowmodctrSQL = CASE WHEN @engineedition NOT IN (5, 6) THEN 'USE ' + QUOTENAME(@dbName) ELSE '' END + '; SELECT @rowmodctr_Out = ISNULL(modification_counter,0), @rows_Out = ISNULL(rows,0), @rows_sampled_Out = ISNULL(rows_sampled,0) FROM sys.dm_db_stats_properties_internal(@statsobjectID_In, @statsID_In) WHERE partition_number = @partitionNumber_In;'

As there are verious places in the code that is expecting the dbname to be quoted I imagine it is probably safer to replace all occurences

''USE ' + QUOTENAME(@dbName) ELSE '' END'

With:

'USE ' + (CASE WHEN CHARINDEX(N'[',@dbName) > 0 THEN @dbName ELSE QUOTENAME(@dbName) END) ELSE '' END

It is a little messy but likely to be safer than trying to do the reverse and figuring out what actually needs to be compared against the quoted value and what doesn't as there are mixed cases throughout.

I will try and get a version submitted as soon as I get some spare time, but hopefully this helps you in the meantime.

[LetsDoSQL-jdw]

It's noteworthy that this error in debug-mode means that the actual Update Statistics functionality of the AdaptiveIndexDefrag does not work, at all. That's how I came about finding this issue. So how come that AndrewG2's suggestion to fix this bug hasn't made it to the original repository over the past 3 years?