Old post I know, but in case anyone runs into this in the future, I've done extensive research into this. It's easy to blame SQL as this is a DB function AX is trying to accomplish, but IT IS NOT SQL! Even our AX vendor immediately blamed SQL until I proved them wrong. My first direction was to look into SQL Index length limits and make sure AX wasn't bumping up against those, I don't recall exactly what I found out but it was something to the effect that SQL's index length limit is way beyond the point that the error message truncates the index name, by a few hundred characters. It's the query AX uses to drop these indexes that truncates the names incorrectly and throws an error when SQL can't find the Index in question off of a partial name.
I started off by simply running the Sync, waiting forever for it to fail then it would list off the indexes it could not drop in the error output, so I would go and just drop those indexes by hand and re-run it and it would complete with only nearly 6 hours of time wasted! I'd much rather let AX do this as there could easily be a good reason to let AX drop and recreate them as it intended (in other words, no good reason other than just not knowing, but it seems to cause the least impact).
After a ton of trial and error, I found that keeping index names + table names (Plus additional joining characters like "_" and ".") at or below 90 characters is the magic number, so before I synchronize I run a script I whipped up to rename them.
Typical disclaimer, this may cause you problems elsewhere, use it with caution (and yes I know this could be a bit tidier and shorter, but I also wanted it to be relatively easy to read):
##This Powershell script will truncate all Indexes that have a full name (Tablename.Indexname) and replaces them with a shortened substring of the end of the Index Name
## I.E. INVENTITEMLOCATION.CST_INVENTITEMLOCATION_WMSPICKINGLOCATION_INVENTDIMID_DATAAREAID_PARTITION
##Becomes: IDX_INVENTDIMID_DATAAREAID_PARTITION_20171221103229
##I used the substring from Right to left to ensure uniqueness in the index name, this isn't perfect but it works.
$PRODSQLInstance = 'YOUR_SQL_SERVER_HOST_HERE'
$PRODSQLDBName = 'YOUR_CUSTOMER_DB_NAME_HERE'
$IndexMaxChar = "90"
$AXSqlServer = $PRODSQLInstance
$SQLGetIdxScriptblock = @"
use $PRODSQLDBName;
SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type
"@
$AllSqlCmd += $SQLGetIdxScriptblock
$SQLIndexesAll = Invoke-Command -ComputerName $SQLServer -ScriptBlock {
param($SQLGetIdx_Query)
sqlps -NoLogo -Command {
param($SQLGetIdx_Query)
invoke-sqlcmd -Query $SQLGetIdx_Query -querytimeout ([int]::MaxValue)
} -args $SQLGetIdx_Query
} -ArgumentList $SQLGetIdx_Query
$VerboseMsg = "Indexes Found: "+$SQLIndexesAll.count
Write-Verbose $VerboseMsg -Verbose
$SQLIndexesRenamed = $SQLIndexesAll | ?{(($_.Index_Name | Measure-Object -Character).Characters + ($_.Table_Name | Measure-Object -Character).Characters + 1) -gt $IndexMaxChar}
$VerboseMsg = "Indexes Found exceeding the max char limit for AX DB Sync: "+$SQLIndexesRenamed.count
Write-Verbose $VerboseMsg -Verbose
if (($SQLIndexesRenamed.count) -ge 1){
$SQLRenameIdxScriptblock = @"
use $CustDestDBName;
"@+"`r`n"
$SQLIndexesRenamed | foreach {
$VerboseMsg = "Generating Index Rename SQLCMD for - "+$_.Table_Name+"."+$_.Index_Name
Write-Verbose $VerboseMsg -Verbose
$CurrentIdxName = $_.Index_Name
$CurrentIdxTable = $_.Table_Name
$CurrentTimeStamp = $(Get-Date -format 'yyyyMMddhhmmss')
if (($CurrentIdxTable+"."+"IDX_"+$CurrentIdxName+"_"+$CurrentTimeStamp).Length -ge $IndexMaxChar){
$NewIdxName = "IDX_"+($CurrentIdxName).Substring(($CurrentIdxName.Length) - ($CurrentIdxTable.Length) - ($CurrentTimeStamp.Length))+"_"+$CurrentTimeStamp
}else{
$NewIdxName = "IDX_"+$CurrentIdxName+"_"+$CurrentTimeStamp
}
$VerboseMsg = "New Index Name - "+$NewIdxName+"`r`n`r`n"
Write-Verbose $VerboseMsg -Verbose
$SQLRenameIdxScriptblock += @"
EXEC sp_rename N'$($_.Table_Name).$($_.Index_Name)', N'$NewIdxName', N'INDEX';
"@+"`r`n"
}
Invoke-Command -ComputerName $SQLServer -ScriptBlock {
param($SQLRenameIdxScriptblock)
sqlps -NoLogo -Command {
param($SQLRenameIdxScriptblock)
invoke-sqlcmd -Query $SQLRenameIdxScriptblock -querytimeout ([int]::MaxValue)
} -args $SQLRenameIdxScriptblock
} -ArgumentList $SQLRenameIdxScriptblock
}
