Tag: PowerShell

Going Into Standby Using Powershell

Since moving to Windows 8 on my primary laptop I have found myself using PowerShell to perform shutdown and restart tasks. For me hitting the charms and going through the options to do a simple shutdown was just way too much work, especially when I could just hit the PowerShell icon and type stop-computer or restart-computer. 

One annoyance I found was that there was no pause-computer or equivalent to put the machine into standby, something very useful for a laptop.

Given that this was something I wanted to do I put something a new function in my profile to give me that ability. Now I can just type pause-computer and it will go into standby. Script below…

Function Pause-Computer 


        Write-Output "Going to standby..."

        Start-Sleep -Seconds 1

        &"$env:SystemRootSystem32rundll32.exe" powrprof.dll,SetSuspendState Standby


How To Log Off of Server 2012

I’m finding little things that are petty annoyances when working with Windows Server 2012.


The most recent one of these is the lack of a logoff option when connected to a server via a remote desktop session. When using the charm you can get the option to Disconnect, Shut down or Restart the server, but not log off.

It turns out that the solution is pretty simple, but not intuitive.

Just open up PowerShell and type logoff.

Automated Backup Tuning

Tuning your backups is a wonderful thing. You could easily reduce the time it takes to backup your databases by 50% of more just by changing a few settings, and yet so few people do it. I asked myself the question why and came up with two answers.

  1. People do not know how to tune their backups.
  2. It takes too long to run through the tuning process.

How to tune your backups

I’m not going to go over methods for tuning your backups in this post. There are people who have done a far better job at both explaining the adjustments you can make and that have created videos to help you understand and perform the process yourself.

My biggest concern was directed at the level of effort required to test all the possible permutations of files, maxtransfersize and buffercount values, after all, who has time to figure all of that out and then capture the data to look at the relative performance characteristics of each one?

I decided that the best way to do this was to create a nice little test harness which would run through all those tests without manual intervention, and then figure out a way to get the output from all of those backups into some kind of meaningful graph for that instant visual on performance differences.

No manual backup tuning for this DBA

The backup testing script

Step one in the automated tuning is a SQL script I created which accepts a few input variables:

  • @DatabaseName – name of the database you want to use for testing
  • @MaxBackupFiles – maximum number of files you want to write to at a time
  • @BackupFolder – destination folder for the backups
  • @WithCompression – whether or not to use SQL Server backup compression
  • @CreateJob – Whether or not to create a SQL Agent job to run the tests
  • @JobLogFileName – file path and name for a log for the job
  • @OutputToScreen – outputs the backup commands to the screen

When executed the script is designed to created SQL Agent job which you can then execute at a time of your choosing. The job will run through a series of backups for a database (at minimum 40) and capture the information in the job log file.

Be warned, performing this many backups test, with so many settings, can and will impact your server performance at some point. Be aware of this. Do not cause issues for your users when performing the tests, they will not like you for doing so.

Visualizing the results

Once the backup testing job is complete a job log file is left behind. This log file contains all of the information for each of the backups performed including the:

  • number of files backed up to
  • maxtranfersize setting used
  • buffercount setting used
  • speed of the backup in MB/sec
  • time the backup took to complete in seconds

Getting that information usable is done using a PowerShell script. A blog post by Jonathan Kehayis described his method for parsing SQLIO output to excel charts. Based on this script, the update version parses the backup log file that has been created, imports the information into excel and creates a couple of charts.

These charts show the speed (Figure 1) and backup time (Figure 2) for the default backup options, the absolute fastest backup to NUL, and the all of the other options that were tested.

Figure 1. Backup speed results for 100GB database backed up across 10Gb network to CIFS share

Figure 2. Backup time results for 100GB database backed up across 10Gb network to CIFS share

The graphs provide a nice visual representation of the backup performance which helps to quickly identify that there are improvements that can be made.

The default SQL backup runs at just over 200MB/sec and takes about 235 seconds to complete, but we know that we can perform that same backup to a NUL device in about 75 seconds at a rate of over 1200MB/sec.

Just looking through the rest of the data it is clear to see that a lot of the values used make a negligible change to the performance of the backups, so we can work to make an informed decision on what the best file, maxtransfersize and buffercount settings should be.

Hopefully this is something that you can find useful. Please give it a try and send me feedback on your testing efforts.

Downloads: Backup Test Harness.sqlParse Backup Perf Tests.ps1

Disabling Windows Updates With PowerShell

Allan Hirt wrote a post back in February entitled “Stop Automatically Updating Production Servers” in which he gave some excellent reasons on why you should ensure that your servers are only updated manually (after all who wants an accidental production down scenario, am I right?).

This hasn’t hit me as a problem as all of my servers are disconnected from the Internet and there’s no WSUS server configured for them to reach, we just push patches down to the machines from a central location. I was perfectly happy with this scenario, and still am, however there is a PITA reason why I wanted to turn off the automatic checks for Windows Updates; constant dumping of events into the System and Application Event Logs:





I was really sick of seeing these errors and wanted a quick way to turn off Windows Update without having to go through the multi-click interface nonsense.

Once more PowerShell to the rescue.

Disable-WindowsUpdates.ps1 does just what it says on the box. It disables Windows Updates on the machine it’s run on (provided that’s allowed by Group Policy). You have to execute it as an administrator. When it done it will remove those pesky alerts from the logs and you can feel free that Windows won’t decide to reboot your servers in the middle of the day to apply that IE update you’ve always wanted.

Comparing Indexes Using PowerShell

When you have data replicated out to multiple machines or are working on migrations it can be a challenge to ensure that the indexes are in sync, thus preventing query performance issues. I found the need to check and sync up indexes myself the other day and I didn’t have any third party software installed to help me do the compare. Yet again PowerShell came to the rescue and I ended up putting together a script that just kind of grew and grew on me, so this morning I decided to stop and put it out for anyone to use.

The script accepts a few parameters:

  • Server1 – The source server that you’ll use as your source of index goodness
  • Server2 – The destination that you want to compare (this can be the same server)
  • Database1 – The source database that contains the tables and indexes
  • Database2 – (this is optional) the destination database to check, if this isn’t passed then this defaults to the same value as Database1
  • OutputScriptLocation – The path and filename for the output SQL script file which you can run to create the indexes
  • Verbose – A switch which will dump lots of information to the screen, which can be useful as it will also tell you if there are missing tables on the destination


I decided not to do the actual index creation within the script as it’s good to be able to check the output and be sure that’s the thing you want. I did however output the script in such a way to make it as easy as possible to run, and so you can just open the script, switch to SQLCMD mode and run it. The script will make the connection to your destination server, to the right database and then create the indexes. It will also output to the screen when an index is created (this is very useful for those times when you are creating a lot of indexes and some of them are large).


I’ve tested it on a few servers and it’s worked out great for me, let me know if you run into any problems running it or encounter strange bugs.

Download Compare-Indexes.ps1 or get the code below.



   Script can be used to compare indexes on tables on different servers/databases.


   When executed the script will check passed in databases on different (or the same) server and output a SQLCMD script which

   can be executed to add missing indexes.

.PARAMETER <paramName>

   -Server1 The source SQL Instance that is used as the master

   -Server2 The destination SQL Instance that will be the target for additional indexes

   -Database1 The source database

   -Database2 The destination database (leave blank if the source and destination databases are the same)

   -OutputScriptLocation Path and filename for the .sql file that will be created for any indexes that need to be added

   -Verbose Output some logging information


   ./Compare-Indexes -Server1 localhost -Server2 remotehostinstance1 -Database MyDB -OutputScriptLocation C:TempNewIndexes.sql -Verbose





[string]$Server1 = "$(Read-Host 'SQL Instance 1 [e.g. (local)]')",

[string]$Server2 = "$(Read-Host 'SQL Instance 2 [e.g. (local)]')",

[string]$Database1 = "$(Read-Host 'Database Name')",


[string]$OutputScriptLocation = "$(Read-Host 'Output File Name')",




if (!$Database2) { $Database2 = $Database1}


$MissingIndexCount = 0




    #Initialize the output file

    $FileInitiation = @"

:ON ERROR exit 

:setvar SQLInstance `"$Server2`"

:setvar Database `"$Database2`"

:CONNECT `$(SQLInstance)


USE `$(Database);





    $FileInitiation | Out-File $OutputScriptLocation

    $File = "True"


catch { Write-Host "Output folder does not exist...aborting script." 

    $File = "False"




if ($File -eq "True")


    if ($Server1 -ilike "**") { $BaseServer1 = "SQLSERVER:SQL$Server1Databases$Database1Tables" }

    else { $BaseServer1 = "SQLSERVER:SQL$Server1defaultDatabases$Database1Tables" }


    if ($Server2 -ilike "**") { $BaseServer2 = "SQLSERVER:SQL$Server2Databases$Database2Tables" }

    else { $BaseServer2 = "SQLSERVER:SQL$Server2defaultDatabases$Database2Tables" } 



    Write-Output "Please wait...comparing $Server1.$Database1 to $Server2.$Database2"


    $Server1List = dir $BaseServer1 | select DisplayName

    $Server2List = dir $BaseServer2 | select DisplayName


    #For information purposes show a list of tables missing on server2 if verbose enabled

    if ($Verbose)


        $MissingTables = Compare-Object $Server1List $Server2List -Property DisplayName | Where-Object {$_.SideIndicator -eq '<=' } | Select DisplayName

        foreach ($MsTbl in $MissingTables)

        {            $MissingTable = $MsTbl.DisplayName

            Write-Output "Missing table - $MissingTable"




    #The compare uses -IncludeEqual as we only want to compare indexes on tables that exist on both servers

    $TableList = Compare-Object $Server1List $Server2List -Property DisplayName -IncludeEqual | Where-Object {$_.SideIndicator -eq '==' } | Select DisplayName

    if ($TableList)


    foreach ($Table in $TableList)


        $MissingIndex = "" #Initialize the variable so we can null it

        Clear-Variable MissingIndex #Null the variable as we'll be checking for a value in here later

        $TableToCheck = $Table.DisplayName

        $Table1Index = dir $BaseServer1$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name

        $Table2Index = dir $BaseServer2$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name

        if ($Verbose) {Write-Host "Checking $TableToCheck"}

        $MissingIndex = Compare-Object $Table1Index $Table2Index -Property Name -IncludeEqual | Where-Object {$_.SideIndicator -eq '<='} | Select Name

        if ($MissingIndex)


            $MissingIndexCount += 1

            $Index = $MissingIndex.Name

            Write-Output "Missing Index - $Index on $TableToCheck"

            dir $BaseServer1$TableToCheckIndexes | where-object {$_.Name -eq "$Index"} |

            % {$_.Script() | out-file $OutputScriptLocation -append; "; `r`nRAISERROR(`'Index $TableToCheck.$Index created...`',0,1) WITH NOWAIT; `r`n`r`n " |

                out-file $OutputScriptLocation -Append;}



    if ($MissingIndexCount -gt 0) {Write-Output "File $OutputScriptLocation created. Open this file in SSMS and execute in SQLCMD mode"}

        else {Write-Output "No missing indexes found!"}


    else { Write-Output "No matching tables found."}


Passing SQL Data To Command Line Processes Within SQL Jobs

Wow, that title is a bit of a mouthful, let me give you a scenario to help understand what the business problem was that I ran in to today.

We have a production database running on SQL 2008 which contains a CLR procedure that accepts a reportID value, queries some data and writes out to the filesystem on a remote share. This procedure is called by multiple daily and weekly jobs to perform extracts for business partners. Yes, I know this is ugly. The database has TRUSTWORTHY on, which is a big security risk and we wanted to mitigate that risk with the minimum amount of work required.

Here’s an example of one of the jobs that makes the call in to that proc:



SELECT  @ReportID = ReportID

FROM    dbo.ReportList

WHERE   BusinessPartner = 'Customer1'

        AND Frequency = 'Daily';


EXEC Data2File @ReportID;


The first step to changing this was to get the CLR code out of source control and rebuild it as an executable file. This took the developer about 60 minutes. Now I had to figure out how we were going to call the executable with the appropriate ReportID.

The obvious way to call this would be to create a cmdline job step for D:ExportExeData2File.exe (the name and location of the new executable). This would be great except that it doesn’t contain the ReportID. The smart move here would be to just pass along the ReportID in the cmdline, except that we don’t know what that is for any particular report as they get deleted and added fairly frequently, we need to actually pass the results of the query along. The cmdline really wasn’t going to help here.

As is frequently the case, PowerShell came to the rescue.

All I had to do was create a PowerShell job step, run the query, pass the result into a variable and then call the executable with the variable. Sounds really simple, it took a few minutes to get it right, in the end I wound up with the following PowerShell script that runs from within a PowerShell job step:

$ErrorActionPreference  = "Stop"


$Query = @"


FROM    dbo.ReportList

WHERE   BusinessPartner = 'Customer1'

        AND Frequency = 'Daily';



$ResultSet = invoke-sqlcmd -ServerInstance MySQLServer -Database MyDatabase -Query $Query -QueryTimeout 30

[int]$RptID = $ResultSet.ReportID

Write-Output "Calling Data2File.exe with ReportID: $RptID"


& "D:ExportExeData2File.exe" $RptId


In this script I’m building the query, calling it with invoke-sqlcmd and then passing the output ReportID to the external executable.

While this is still pretty ugly and not the way that this sort of thing should be done (SSIS anyone?) it does work and more importantly it allows me to turn off the trustworthy setting on that database and improve the security on my SQL Servers.

Changing SQL Agent Job Subsystem Queue Lengths

In the event you are running a lot of jobs on your SQL Server, or you happen to have a great number of jobs that kick off close to each other and use the same subsystem (i.e. PowerShell) then you might receive a warning in the SQL Agent Error Log stating that the job step “is being queued for the PowerShell subsystem”.

This queuing is deliberate. Microsoft put in safeguards around the various subsystems that the Agent calls in order to help prevent any particular subsystem from taking over all the worker threads (after all you wouldn’t want your 25 PowerShell job steps preventing your queries from running because they’d taken all the worker threads would you?)

This is one of the reasons that I like to use dedicated servers just to handle all jobs/ETL processes. I like to keep my local jobs to just maintenance processes (backup/reindex/checkdb) wherever possible. In doing this I need to be sure that I am able to increase the max worker threads associated to a subsystem to be sure that jobs don’t get delayed. As such I keep an eye on the SQL Agent Error Log and server resources to check that I don’t get thread starvation issues (and being just a job server that’s pretty unusual).

Today I saw a bunch of warnings in the log to do with PowerShell job steps being queued. This wasn’t a surprise as I had added a whole bunch of new jobs and had not adjusted things from the default values. First I went through and grabbed the default information for each of the subsystems so that I could easily revert if the need arose:

SELECT [subsystem_id]



  FROM [msdb].[dbo].[syssubsystems]



As you can see here there are only 2 worker threads set for PowerShell. I need to bump this up to 20 so that it could handle the concurrent job load. I have plenty of memory available on the server so I’m not concerned with the overhead associated with the loading of PowerShell either. Always something you need to keep in mind (oh and you have to be a sysadmin to be able to update this table).

UPDATE msdb.dbo.syssubsystems

SET max_worker_threads = 20

WHERE subsystem = N'PowerShell'  


Checking the value again yields the updated information:

SELECT [subsystem_id]



  FROM [msdb].[dbo].[syssubsystems]



Now it’s a simple case of restarting the SQL Server Agent service for the change to take effect.

Note: Applying Service Packs or Cumulative Updates could potentially reset this value, so make sure you document the change and confirm it after any upgrade (for example http://support.microsoft.com/kb/972759 )