Path/File Name Length Issue

[timothy-eichman-tfs]

Discovered this last week on a server that hosts SharePoint databases (ya know, the one's with the GUIDs in the database name).

The maximum path length check (~Line 2500 [below]) doesn't take into consideration the cluster/AG name when computing if the path will be too long. Since the cluster/AG is used in the path and prefixed to each database name, the length should be counted a second time (it's included in the DirectoryPath by the time this code gets executed.

Here's an example with the actual names replaced to protect the innocent...

• Directory: \the-cifs-share-server.with.fqdn\zone1_sql_prod_cifs\DAILY
• Cluster: longclustername
• AG Name: somerealylongagname
• Database Name: unnecessarily_long_name_a_dba_would_never_make_ever_up_ubuiqiuitoussharepointguidinname

That setup produces this which is 263 characters long: \the-cifs-share-server.with.fqdn\zone1_sql_prod_cifs\DAILY\longclustername$somerealylongagname\unnecessarily_long_name_a_dba_would_never_make_ever_up_ubuiqiuitoussharepointguidinname\FULL\longclustername$somerealylongagname_unnecessarily_long_n..._FULL_20221106_230100.bak

That in turns results in this:

Msg 3057, Sev 16, State 1, Line 1 : Invalid device name. The length of the device name provided exceeds supported limit (maximum length is:259). Reissue the BACKUP statement with a valid device name. [SQLSTATE 42000] Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

` SELECT @CurrentMaxFilePathLength = CASE WHEN EXISTS (SELECT FROM @CurrentDirectories) THEN (SELECT MAX(LEN(DirectoryPath + @DirectorySeparator)) FROM @CurrentDirectories) WHEN EXISTS (SELECT FROM @CurrentURLs) THEN (SELECT MAX(LEN(DirectoryPath + @DirectorySeparator)) FROM @CurrentURLs) END

• LEN(REPLACE(REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',@CurrentDatabaseNameFS), '{FileNumber}', CASE WHEN @NumberOfFiles > 1 AND @NumberOfFiles <= 9 THEN '1' WHEN @NumberOfFiles >= 10 THEN '01' ELSE '' END)) `

[Keith-MacLure]

I am currently experiencing this exact issue.

I was thinking of maybe mapping the long directory\folder structure to a drive and then just using the drive letter location but how to do it per use or file?

Also the permissions to create mapped drives from SSMS / code is problematic.

[DBAHAFIZ]

I have exact the same issue, can anyone please help

[griffitmatt]

This is a warning message returned by the SQL server and not an issue with the OLA backup script. The limit is set by Microsoft SQL server and cannot be changed. To be able to meet the limit you need to look at options to reduce the length. For example, relocating the backups to a shorter path.

[olahallengren]

I am looking at the code for this issue.

Does anyone still have a repro?

[timothy-eichman-tfs]

Unfortunately, the server that I had that had the issue with back in 2022 has since been retired, and the only other clustered server we have with SharePoint on it is backed up via an Image-level backup tool...

I forgot that we came up with a workaround, and I'm surprised that I found that in my SQLPrompt history...

We replaced this:

-- The maximum length of a backup device is 259 characters IF @CurrentMaxFilePathLength > 259 BEGIN SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 259 - @CurrentMaxFilePathLength - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 259 - @CurrentMaxFilePathLength - 3) END) + '...') END ELSE BEGIN SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',@CurrentDatabaseNameFS) END

with this:

-- The maximum length of a backup device is 259 characters IF @CurrentMaxFilePathLength > 259 BEGIN /* Workaround for cluster and long file name --> */ IF (NULLIF(@cluster, '') IS NOT NULL) BEGIN SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,@Cluster+'$'+@CurrentAvailabilityGroup+'_', '') SET @CurrentMaxFilePathLength = @CurrentMaxFilePathLength - LEN(@Cluster+'$'+@CurrentAvailabilityGroup+'_') END /* Workaround for cluster and long file name <-- */ SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',LEFT(@CurrentDatabaseNameFS,CASE WHEN (LEN(@CurrentDatabaseNameFS) + 259 - @CurrentMaxFilePathLength - 3) < 20 THEN 20 ELSE (LEN(@CurrentDatabaseNameFS) + 259 - @CurrentMaxFilePathLength - 3) END) + '...') END ELSE BEGIN SET @CurrentDatabaseFileName = REPLACE(@CurrentDatabaseFileName,'{DatabaseName}',@CurrentDatabaseNameFS) END

[olahallengren]

@timothy-eichman-tfs, thank you for sharing this code.

The maximum path length check (~Line 2500 [below]) doesn't take into consideration the cluster/AG name when computing if the path will be too long.

From what I can see, the check in DatabaseBackup works the same way, regardless of availability groups or standalone databases. You can see that where @CurrentMaxFilePathLength is being calculated.

I forgot that we came up with a workaround, and I'm surprised that I found that in my SQLPrompt history

My understanding is that my logic (shorting the database name to 20 characters, plus '...') was not enough to bring the path down 259 characters.

Your logic then takes additional measures by removing the cluster name and availability group name from the file name.

[olahallengren]

One possible workaround could be to change the file name using @AvailabilityGroupFileName. Then you could remove the cluster name and availability group tokens. https://ola.hallengren.com/sql-server-backup.html#AvailabilityGroupFileName