GOTCHA: Executing powershell scripts using scheduled tasks


This is another gotcha for setting up scheduled tasks which execute PowerShell scripts. I have a SQL Server instance installed on an Azure virtual machine. I am using a D-Series machine which allows me to store my tempdb files on the D: drive which is a SSD drive. However, the D: drive on Azure virtual machines is not a persistent drive. If you have change the drive letters on your Azure VM, then you can use the PowerShell script in my earlier blog post to identify the temporary drive. So, when the Azure virtual machine restarts the D: drive is re-created and all my folder structure is lost. I already have a scheduled task created on my Azure virtual machine which re-creates the folder structure on the D: drive. The blog post in the reference section has more details on how to achieve this.

However, when the scheduled task executes, the following error is reported in the Task Scheduler logs.

image

The last run result is reported as 0xFFFD0000 and the task history would show the following message:

Task Scheduler successfully completed task “\Tempdb Folder Creation” , instance “{35ec7a4f-6669-437f-b12f-40b95689896c}” , action “C:\Windows\System32\WindowsPowerShell\v1.0\powershell.EXE” with return code 4294770688.

First, let us find out why this error message gets reported. If you have setup a PowerShell script to execute the script in the following manner using single quotes, then this issue occurs.

powershell -file ‘C:\Automation\TempdbFolder.ps1’

If you have not used an output file, then you will find that this might not be an easy thing to troubleshoot. If you execute the above command from a command prompt window, you will find the following error reported:

Processing -File ”C:\Automation\TempdbFolder.ps1” failed: The given path’s format is not supported. Specify a valid path for the -File parameter.

Changing the command to use double quotes would make it execute without any issues.

Some other things to keep in mind when creating the scheduled task would be:

1. Using a full qualified path to the script file rather than a relative path.

2. Ensuring that the account running the script has the correct privileges.

3. The task should be configured to run without having the user logged in.

Reference:
Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions
http://blogs.technet.com/b/dataplatforminsider/archive/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions.aspx

Advertisements

3 thoughts on “GOTCHA: Executing powershell scripts using scheduled tasks

  1. Pingback: SQL Server Scheduled Tasks and PowerShell | Senior DBA

  2. Thanks! I was hit by this one myself, as I so often lean towards using single quotes instead of double quotes!

    Like

It is always good to hear from you! :)

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