Getting "Execution Timeout Expired" Running SQL Server Backup through Invoke-Sqlcmd

Question

Disclaimer: I'm a newbie with Powershell.

I am trying to script the backup of two SQL Server Express databases using a SQL script run from Powershell. When I run the SQL From SSMS, it runs fine with no timeout. When I run the SQL from a Powershell script the small database backup succeeds, but larger db backup fails with this error:

Invoke-Sqlcmd : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
At C:\Program Files\DBBackups\dbfull_backup.ps1:5 char:1
+ Invoke-Sqlcmd -ServerInstance "$serverInstance" -InputFile "C:\Program Files\ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Here is line 5 from the .ps1 script which has the Invoke-SqlCmd:

Invoke-Sqlcmd -ServerInstance "$serverInstance" -InputFile "C:\Program Files\DBBackups\dbfull_backups.sql" -Variable $sqlVariable -Verbose *> $outputFile

Here is the BACKUP command from the SQL:

BACKUP DATABASE FrameStore TO DISK = @framestore_backup_file;

Is the timeout different when run through Powershell/Invoke-Sqlcmd than from SSMS? When run in SSMS, the backup takes under 3 minutes so not close to the 10 minute number I saw in the docs.microsoft documentation. Here is the backup output when run in SSMS:

BACKUP DATABASE successfully processed 467003 pages in 176.917 seconds (20.622 MB/sec).

Version info:
OS: Windows Server 2012 R2
MS SQL Server Express 2017
PSVersion 4.0

(My apologies if this is already addressed in another SO page. I couldn't find this specific issue although it seems pretty basic.)

EDIT:
I went ahead and tried adding the -QueryTimeout parameter set to 3600 seconds. This allowed the backup to succeed without the error. So, what is the default?

According to Microsoft Docs page for Invoke-Sqlcmd -QueryTimeout parameter:

Specifies the number of seconds before the queries time out. If a timeout value is not specified, the queries do not time out.

So... if that is true and I didn't specify the timeout, why is it timing out? Did this change from PS version 4 to the version documented? Does it seem like bad practice to set a timeout on a backup? I'm interested in opinions on this. It works now, but I'm still not confident in my solution (work-around?).

Community · Accepted Answer · 2019-03-26 19:03:37Z

20

So you have hit the famous 30 seconds timeout of the SMO object.

Grant Fritchey wrote about it back 2010. Read about it here: https://www.scarydba.com/2010/04/16/powershell-smo-problem/

If you set it = 0 - You will have told it to run infinite.

A more recent blog post about the same: https://fbrazeal.wordpress.com/2015/06/29/sqlps-tips/

Edited:

Depending on what version you're running, you should read this also: https://blogs.technet.microsoft.com/heyscriptingguy/2013/05/06/10-tips-for-the-sql-server-powershell-scripter/

edited Mar 26, 2019 at 19:03

CommunityBot

11 silver badge

answered Sep 28, 2018 at 10:32

Mötz

1,74212 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eduardo Wada Over a year ago

The second link to the wordpress site is now dead.

Raghavendra · Accepted Answer · 2019-07-11 14:43:22Z

19

Long question short answer you need to use parameter -Querytimeout X where X is time in seconds

query in question will work like this where 0 make infinite timeout we can use an integer value between 1 and 65535.

Invoke-Sqlcmd -ServerInstance "$serverInstance" -Querytimeout 0 -InputFile "C:\Program Files\DBBackups\dbfull_backups.sql" -Variable $sqlVariable -Verbose *> $outputFile

answered Jul 11, 2019 at 14:43

Raghavendra

1,5074 gold badges20 silver badges28 bronze badges

Comments

Stelios karapis · Accepted Answer · 2024-10-25 08:32:41Z

1

There is also the parameter -ConnectionTimeout which can be set for example to the same value as the -QueryTimeout parameter. For me ConnectionTimeout parameter was releasing the connection to the db thus the query I was running by Invoke-Sqlcmd . When I set it to higher value it worked.

answered Oct 25, 2024 at 8:32

Stelios karapis

111 bronze badge

Collectives™ on Stack Overflow

Getting "Execution Timeout Expired" Running SQL Server Backup through Invoke-Sqlcmd

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related