Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Powershell secret timeout ! Running SQL from powershell V1

Posted by rahmanagoro on August 26, 2010


I have always been very keen to learn new aspects of SQL server and as much as possible use new technology. When SQL 2008 came out about 2 years ago, powershell technology has always been one that’s interests me, I like the fact that almost any task can now be scripted. I liked the fact that, I can write a script and push it out to as many SQL instances as possible. I also like the fact that tasks in windows can also be scripted as well, so lets say I want to collect perfmon data and integrate this with SQL server, it is now easy to achieve this.

To this note, I was inclined to use powershell towards running backup stored procedures, although I could have easily used TSQL to do this, but as I know, powershell is the future and as a DBA, I don’t want to be left behind. I also liked the fact that, within my backup scripts I want to delete older backups after they have been backed up to tape, powershell allows me to easily do this without having to write lines of scripts.

I found a sample scripts on:

To my surprise, I started to run my powershell scripts which backs up SQL databases via stored procedures, all of a sudden they started failing. The stored procedure was fairly straight forward and simple, but yet the scripts will still fail. What I noticed was that for differential backups, the powershell script runs fine, but for full backups the powershell scripts which calls the stored procedure fails. This occurred to me like some kind of issue with timeout, at first it sounded strange as I didn’t specify a timeout period, but yet the query was failing.

Even a simple query like the one below will fail as well

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "  waitfor delay '00:00:50'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]


I later confirmed from Microsoft MVP Grant Fitchley that there is a 30 second default timeout, this is something that everybody should be aware of in order to avoid this problem. I also note that a connect ticket has been opened for this bug, see https://connect.microsoft.com/PowerShell/feedback/details/523996/invoke-sqlcmd-querytimeout?wa=wsignin1.0

As a workaround, a timeout value was passed into the connection properties.
If a timeout value of 0 is specified, then the script will not time out.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = " waitfor delay '00:00:35'"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = 0
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: