I'm trying to automate executing SQL scripts using Invoke-SQLCmd powershell command. As a source I have a one script generated by SSDT Schema Compare (4000 lines, all inside transaction). I'm triggering this script in the following way:
Invoke-SQLCmd -InputFile "$Path" -ServerInstance "$Server" -Database "$DbName" -ErrorAction "Stop"
As an output I'm receiving the following error:
Invoke-SQLCmd : Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. [...]
I know that this problem is probably related to lack of free space on testdb disk, but what is interesting the script has been completed successfully when it was executed from SQL Server Management Studio on the same database.
The question is how is it possible and how can I make this script working correctly from PowerShell.
This error is due to insufficient Transaction log space...
You can use below command to find out offending transaction and take care of it
This may be also due to your script generating a lot of transaction log and you dont have enough Transaction log space left...if this is the case, you might need to break up your command into multiple commands or increase your tlog space