Azure DevOps if failing at download artifacts step in release pipelines

I've lot of SQL Scripts in Azure Repos and the size of each sql script is under 100MB, I'm just deploying them on to Azure SQL Database via release pipeline in ADO. But in release pipeline it's failing at download artifacts step with error message

error: unable to write file Folder/Folder01/insert.sql

Note: switching to '98a9aaca00......'.

You are in 'detached HEAD' state. You can look around, make experimental
changes and commit them, and you can discard any commits you make in this
state without impacting any branches by switching back to a branch. 
If you want to create a new branch to retain commits you create, you may
do so (now or later) by using -c with the switch command. Example:
git switch -c <new-branch-name>
 Or undo this operation with:
 
   git switch -

 Turn off this advice by setting config variable advice.detachedHead to false
 
 HEAD is now at 98a9aac Merged PR 8679: Upload Test Data on <Repo Name>
 An error occurred during download: System.InvalidOperationException: Git checkout failed with exit code: 1
   at Microsoft.VisualStudio.Services.Agent.Worker.Build.GitSourceProvider.GetSourceAsync(IExecutionContext executionContext, ServiceEndpoint endpoint, CancellationToken cancellationToken) in D:\a\_work\1\s\src\Agent.Worker\Build\GitSourceProvider.cs:line 869
   at Microsoft.VisualStudio.Services.Agent.Worker.Release.Artifacts.TfsGitArtifact.DownloadAsync(IExecutionContext executionContext, ArtifactDefinition artifactDefinition, String downloadFolderPath) in D:\a\_work\1\s\src\Agent.Worker\Release\Artifacts\TfsGitArtifact.cs:line 54
   at Microsoft.VisualStudio.Services.Agent.Worker.Release.ReleaseJobExtension.<>c__DisplayClass40_2.<<DownloadArtifacts>b__3>d.MoveNext() in D:\a\_work\1\s\src\Agent.Worker\Release\ReleaseJobExtension.cs:line 295
--- End of stack trace from previous location ---
   at Microsoft.VisualStudio.Services.Agent.Worker.Release.RetryExecutor.ExecuteAsync(Func`1 action) in D:\a\_work\1\s\src\Agent.Worker\Release\RetryExecutor.cs:line 64
2025-03-21T12:05:37.3477405Z Retrying download...
2025-03-21T12:05:37.3486081Z Ensuring artifact folder D:\a\r1\a\_<RepoName> exists and is clean.
2025-03-21T12:05:38.3464462Z Syncing repository: c685c8a2-b563-4ef3-8cf1-2a3a43e4c385 (Git)

I've even enabled Checkout submodules to true, but still no luck.

Could someone help please ?

Based on the fact that it works on ubuntu-latest but fails on windows and the error related to the git, there are some issues in your repo related to git that can be handled by Ubuntu but not Windows. I have noticed similar issue, for example, this one.

As a workaround, you can use ubuntu-latest image to run your pipeline. For running Invoke-Sqlcmd command, add a command line task before your PowerShell task to install SqlServer module.

pwsh -Command "Install-Module -Name SqlServer -Scope CurrentUser -Force -AllowClobber"

Run Get-Module SqlServer -ListAvailable in a later PowerShell task to verify that the module has been installed successfully. Then you can run Invoke-Sqlcmd in your PowerShell task.

If you don't want to use Ubuntu-latest image, you should first identity and resolve the git issue in your repo.