CTRL plus ALT plus what?

2014/09/15 § Leave a comment

Depending on how far back you go in the technology field you all remember having to use CTRL+ALT+DELETE to change your password for your domain account.


Well, I always remote into servers now a days with consulting for various clients. I never like to keep the password assigned and generally will attempt to change it using PowerShell:

$cn = gpresult /R | Where {$_ -match “CN”} | Select -Skip 1 | clip
$acct = [adsi]“LDAP://$cn”

Most of the time though I get access denied so how can you change it over VPN, when your machine is not part of their domain? Well starting in Windows Server 2008 (I think) Microsoft added a new key sequence that acts like CTR+ALT+DELETE for that machine.

You can now execute CTRL+ALT+END to get to the prompt to change your password. On Window Server 2012 it looks similar to this:


Click on “Change a password” and you get the similar prompt to older versions of Windows:


SQL Server Activity Monitor Script

2014/07/30 § Leave a comment

You ever wonder what goes in the background of Activity Monitor? Me neither, I just used it every so often when I wanted a quick peek at what was going on for a server. Microsoft has actually made changes to Activity Monitor in SQL Server 2012 Service Pack 1 that can cause errors, and requires you to modify OS level permissions [Reference]. I work over VPN supporting clients remotely and it never been that useful to me.

I liked all the information it showed but just did not like having to use the UI it provided to filter and such. So I did a trace against an instance while I opened Activity Monitor to pull out what query it was running to populate the active sessions.

So this post is pretty much just a bookmark for the script:

SELECT [Session ID] = s.session_id
	,[User Process] = CONVERT(CHAR(1), s.is_user_process)
	,[Login] = s.login_name
	,[Database] = ISNULL(db_name(p.dbid), N'')
	,[Task State] = ISNULL(t.task_state, N'')
	,[Command] = ISNULL(r.command, N'')
	,[Application] = ISNULL(s.program_name, N'')
	,[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0)
	,[Wait Type] = ISNULL(w.wait_type, N'')
	,[Wait Resource] = ISNULL(w.resource_description, N'')
	,[Blocked By] = ISNULL(CONVERT(VARCHAR, w.blocking_session_id), '')
	,[Head Blocker] = CASE 
		WHEN r2.session_id IS NOT NULL
			AND (
				r.blocking_session_id = 0
				OR r.session_id IS NULL
			THEN '1'
		ELSE ''
	,[Total CPU (ms)] = s.cpu_time
	,[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024
	,[Memory Use (KB)] = s.memory_usage * 8192 / 1024
	,[Open Transactions] = ISNULL(r.open_transaction_count, 0)
	,[Login Time] = s.login_time
	,[Last Request Start Time] = s.last_request_start_time
	,[Host Name] = ISNULL(s.host_name, N'')
	,[Net Address] = ISNULL(c.client_net_address, N'')
	,[Execution Context ID] = ISNULL(t.exec_context_id, 0)
	,[Request ID] = ISNULL(r.request_id, 0)
	,[Workload Group] = N''
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT JOIN sys.dm_os_tasks t ON (
		r.session_id = t.session_id
		AND r.request_id = t.request_id
			PARTITION BY waiting_task_address 
ORDER BY wait_duration_ms DESC ) AS row_num FROM sys.dm_os_waiting_tasks ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1 LEFT JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id) LEFT JOIN sys.sysprocesses p ON (s.session_id = p.spid) WHERE s.is_user_process = 1 ORDER BY s.session_id;

SSMS 2012–Object Reference issue with Maintenance Plans

2014/07/21 § Leave a comment

I am amazed at some of the information you can find on the Internet when searching for an issue. Especially with forum posts that mention your issue (or flat out are the exact same), but do not include any final resolution. The best one I came across was this one on SQLTeam’s forum site, the post by “prett”, and note that this is posted back in 2013:

The Maintenance Plan is actually built with few services which Microsoft releases such as SSIS and SQL Server Job Agent, hence if you want to schedule the maintenance plan then your server needs to have SSIS in order to build the maintenance plan, and Job Agent in order to run at regular periods.
This error message “Object reference not set to an instance of an object” occurs because of one of the required component was not available on the server.

SQL Server 2005 started out requiring SSIS to be installed for maintenance plans to run, however when Service Pack 2 was released they removed that requirement. As far as I know after that any release of SQL Server kept that same functionality.

Well I came across this issue on my VMWare View desktop that I utilize for one particular client. I was getting this pretty little box every time I tried to create a new maintenance plan on a newly built SQL Server 2012 Failover Cluster Instance:


The version of SSMS I was using:image

I thought I would apply Service Pack 2 to try and resolve it and noted during that installation it was showing “SQLExpress”. Which did bring to mind that I had installed SQL Server 2012 Management Studio for Express when it was announced that it was the full version of SSMS now. I confirmed this by going to the “Setup Bootstrap” and checking the Summary files for the previous installations on the desktop. I found this:


So I went to Programs and Features and removed it. I then located the installation media for SQL Server 2012 Standard Edition and installed the Management Tools. Then for good measure applied Service Pack 2 for SQL Server 2012:


Low and behold, that solved my issue. I hope those in the future can find this bit of information useful.

PS C:\Check-InstanceConfig (part 2)

2013/12/09 § Leave a comment

In the previous post I discussed the data table that holds the default values for the configuration options in SQL Server 2000 up to SQL Server 2014.

In this post I will go over the function(s) that are included in the script. I will keep the same format as I did in the previous post:

  • How it was written
  • What it contains
  • A few examples

How it was written

If you have already downloaded the script you notice it had one function. I was attempting to make the output similar to what Mike did in his T-SQL script with the text output. After looking at it a bit more and thinking on best practices for PowerShell, I chose a different route. I always try to ensure whatever my functions output, you can do something with them down the pipeline. In order to keep that going I opted to break this up into separate functions. I also included a function that allows you to just pull the raw configuration options from the instance as you wish.

What it contains

There are 4 functions included in the current version of the script:

  • Pull-SqlConfig – Just pull the configuration options information on the instance
  • Check-SqlRunConfig – Compare the running value to the configured value
  • Check-SqlRunDefault – Compare the running value to the default value
  • Check-SqlBadDefaults – Compare the running value to the default value for specific options.

I decided to also include help text with each function so loading the script into your PowerShell session you can simply use the Get-Help cmdlet and it will show you the information on each function. Adding the “-full” switch to the command will output more detailed information as well.


A few examples

As mentioned above the function help information also includes a few examples of each function. So if you want to see them you can simply use the Get-Help function along with the “–Examples” switch.


Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

PS C:\Check-InstanceConfig (part 1)

2013/12/04 § 2 Comments

I was reading a blog post Mike Walsh published on Linchpin People last week: SQL Server Configuration Check Script.

In that script he is, as the title says, checking the configuration of a SQL Server instance for particular things like those options not set to default values and a few particular ones that still are set to default. I was intrigued by this and by the time I got to the end of the blog post was like “bet I can do that in PowerShell too”. I had a slow weekend with the holidays so thought I would sit down and see what I could come up with.

Well I came up with two specific things that I wanted to accomplish with this script:

  1. Provide a reference point to the configuration options for each version of SQL Server
  2. Provide a function that will take that reference point and validate it against any instance

If I tried to put all the information I want to help explain the script this blog post would be longer than you have the time to read through. I decided to just break it up into two parts based on the list above. So this post will just be on the reference point I wrote and hopefully by the end you will have learned the following things:

  • How it was written
  • What it contains
  • A few examples

A few caveats about the script: I did not do anything special in PowerShell that is specific to any particular version. This script should work on PowerShell 2.0 and higher. You will notice if you open this script up in the PowerShell ISE 3.0 or higher I did use regions, just a formatting feature in the ISE. It is treated as normal comment lines outside of the ISE.

One more thing: There is more than one way to do things in PowerShell, this is just the way I decided to go. As well I did not cut corners in this script. I wanted to make sure every command and variable was understood. I tried to not use any aliases with commands (e.g. “?” instead of where or where-object).

How it was written

The first section I put in the script was a comment block that contains the BOL links for the “Server Configuration Options (SQL Server)” page of each version:


I went back and forth a few times on how to actually build out a list of the configuration option names and default values based on the current versions of SQL Server that are available. I knew I needed the information in order to validate it against a SQL Server instance, no matter what version I might try. In Mike’s script he simply scripted out temporary tables with this information. So I kept to the same concept and decided to use a data table (System.Data.DataTable).

What it contains

I tried to make the columns self-explanatory so the column naming convention I chose:

  • ConfigOption – name of the configuration option in sp_configure or sys.configurations with new versions of SQL Server. The list contains those configuration options available from SQL Server 2014 to SQL Server 2000, as stated in the associated links in BOL for each.
  • Default_<4 digit year of SQL Version> – there is a column for each version of SQL Server the script will support (e.g. Default_2012). If a ConfigOption name is not supported or included in any particular version this value is set to –9 (negative nine).


An example of the –9 usage, “access check cache bucket count” is a configuration option only available in SQL Server 2008 and higher:

A few Examples

Ok, I am going to assume the reader is already familiar with how to run PowerShell scripts. I prefer the dot sourcing method in most cases. You can also simply copy and past this into your profile if you desire. Remember, Out-GridView is your friend.

  • To list out the complete data table execute this command:
    $sqlConfigDefaults | Out-GridView
  • I have variables already in the script to list out each version (reference lines 723-729 in the script). So if you wanted to list out the options for SQL Server 2014:
    $sql2014_Defaults | Out-GridView
    I could have filtered out the columns for the other versions, but I thought it to be a nice reference point to see those options that are still around from lesser versions. You can adjust this if you wish, but note that these variables are utilized in the function as well.

Here is the full script. This link will always point to the current version of the script. My next blog post will explain the function in the script.

PS C:\> Calc-MaxSample

2013/11/13 § Leave a comment

I am working on building a function that will allow me to gather performance counter data across multiple servers. I knew this had done by someone in the SQL Server family and went to search the inter-web. I came across Aaron Bertrand’s (blog | twitter) blog post here.

This gave me a starting point, however I also noticed that with the use of the basic Get-Counter cmdlet you provide the –MaxSamples value to tell PowerShell how long you want to collect counter information.

You would want to calculate this based on the –SampleInterval value. A few examples of this can be found in the comments on Aaron’s blog post. If you want to cover a 2 hour period and get a sample every 30 seconds, you would set MaxSamples to 240.

This is a mathematical equation that I did not want to have to keep doing every time. So I decided to build a small function that I added to my PowerShell ISE profile. I don’t use the the console all that much anymore with PowerShell 4.0. The below function includes a bit of help information as well. So, once you load this function into the console or your profile you can use Get-Help cmdlet if needed.

   Assist calculating the max sample for Get-Counter
   Calculates from the sampling value and the hours desired what the max sample value should be for the Get-Counter cmdlet
   Cal-MaxSample -secSample 15 -hours 3
   This example based on the SampleInterval and the number of hours desired output what the MaxSample value should be
.PARAMETER secSample
    The SampleInterval value to be used in Get-Counter command, Int32 value between 2 - 60. The default value for
    Get-Counter command is 1 (one)
    The number of hours desired for capturing performance counter data with Get-Counter command
function Calc-MaxSample
    [int]$calValue = (($hours * 60) * 60) / $secSample
    Write-Host "Based on following calculation: (($hours * 60) * 60) / $secSample)" -ForegroundColor Yellow -BackgroundColor Red
    Write-Host "MaxSample value = $calValue" -ForegroundColor Yellow -BackgroundColor DarkGray

PowerShell Tidbit #492

2013/11/05 § Leave a comment

I do remote support work now as a DBA and have clients that provide my password for VPN login or Active Directory login. I usually access their network through a central remote server that I will use to manage their SQL Server environment. As some of you may know there is no way to change your password through remote desktop until it expires and prompts you to upon login. Depending on the version of Active Directory they are running you may get a little bubble down by the clock that you can use as well, but I tend to miss that most of the time.

So I wanted an alternative so I could change it when I needed to, as some clients do not have as strict a policy regarding password changes as I would like. I took the web and came across a nice post by the Scripting Guys here.

I was not really interested in using this as a function, just needed the commands to use. This article has the exact thing you need so go read it…

The one thing I needed to add was knowing the full AD path of my user account. You all know what yours is right?

Well you can use “gpresult /R” to get that information. All I did was put a bit of PowerShell flare into it. If you are not familiar with the results of this command go ahead and issue it on your computer, if you are part of a domain. It will give you the domain information about the computer and your current login. I like looking at this every so often to check the policies that are applied in the domain.

Anyway there are two AD paths that are returned with this command, I only want the second one. It always returns the computer account first and then your login. So this command provides me that and puts in the clipboard for me:

gpresult /R | Where {$_ -match "CN="} | select -Skip 1 | clip

Combine this with the commands from the article:

$acct = [adsi]"LDAP://<paste result here>"

You may normally see me write functions for this, but for this particular area I like to see it as it works, just to be safe. You may also see the “invoke” command take a while on some domains, just depends on the architecture I guess.

Once you hit enter on the CommitChanges command your password has been updated.


Get every new post delivered to your Inbox.

Join 26 other followers