Purge orphan MDW query plans

The solution that Microsoft proposes to remove orphan query plans from the Data Collector was running too slowly.

After looking at the query plan, I created an index to help with the delete statements.  After this, it should run quickly.

CREATE NONCLUSTERED INDEX [ix_sql_handle]
ON [snapshots].[query_stats] ([sql_handle],[statement_start_offset],[statement_end_offset],[plan_generation_num],[plan_handle],[creation_time])

Using the second index, it runs very quickly.

 

Posted in Uncategorized | Leave a comment

Powershell ComboBox DropDownList

In order to prevent users from typing in a ComboBox, I saw the advice to use this code:
$ComboBox.DropDownStyle = ComboBoxStyle.DropDownList;

But got this error:
ComboBoxStyle.DropDownList not recognized

In order for Powershell to recognize it, use this syntax:
$ComboBox.DropDownStyle = [System.Windows.Forms.ComboBoxStyle]::DropDownList;

Posted in Uncategorized | Leave a comment

CommVault XML automation

I am trying to automate restores using CommVault.  Part of that is to generate XML files.  I’ll circle back here when I’m done with programming it.  Or maybe I’ll have my company sell it.  Probably no one wants it though.  Anyway, cut to the chase…

When the batch file was calling the XML file that I created, I was receiving this:
“execute: Error 0x908: Invalid XML Input. Please check the input.”

When I checked, my files were exactly the same, even special characters.

It turned out to be a difference in encoding.  CommVault was looking for utf-8.   My file, created from Powershell, was encoding in ucs-2.

To rectify this, I changed the encoding of the file with Powershell by using the encode flag.

$XMLOutput | Out-File “RestoreParameters.xml” -Encoding “UTF8”;

Posted in Uncategorized | Leave a comment

SQLPS warning message

WARNING: Failed to load the ‘SQLAS’ extension: SQL Server WMI provider is not available on SERVERNAME

I was getting this error when loading SQLPS from a job.  I never did figure out the root cause.  It may have something to do with SQL Server 2008 management objects being installed along with SQL Server 2014 components.

I just suppressed the warnings using the WarningAction flag

Import-Module -Name SQLPS -DisableNameChecking  -WarningAction SilentlyContinue

Posted in Uncategorized | Leave a comment

Restore history

For MSDB Restorehistory column restore_date, the SQL documentation claims that this means “Date and time of the completion of the restore operation.”  However, this doesn’t appear to be the case.  It shows the start time.

This is bad, because I’m trying to kick off an email based on the restore completion time.  It is looking for restores completing in the past 15 minutes.  It wasn’t finding any, because this restore_date is over an hour ago, even though the restore just completed.

Another indication that a restore took place, is that my job took more than a minute.  And my email gets kicked off in the second step.  So it will now check to see if the job has been running more than an hour, and if so, it will send the email.  Here is the new code.

DECLARE @RunTime int
SELECT @RunTime = DATEDIFF(N, start_execution_date, GETDATE())
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL
AND sj.name = 'Restore Databases'

IF @RunTime > 60
BEGIN

DECLARE @text NVARCHAR(2000)
SET @text = 'The restore completed on ' + @@SERVERNAME
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile'
, @recipients = 'daniel@glutenfree.com'
,@subject = 'Restore completed'
,@body = @text

END
Posted in Database Administration | Tagged , , , | Leave a comment

SetParent failed for Login

I’m back for another boring Powershell blog post, that might help some people

I was getting this strange error:

New-Object : Exception calling “.ctor” with “2” argument(s): “SetParent failed for Login ‘GFreeSQL’. ”
At line:1 char:22
+ $WindowsGroupLogin = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Log …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
+ FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

It turns out that I needed to specify the domain in the server name, because I was running it from a different domain.
So it should look like this:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null;
$servername = “sqlserver12345.gfree.lan
$ServerObject = new-Object Microsoft.SqlServer.Management.Smo.Server($servername )
$LoginName = “GFreeSQL”;
Microsoft.SqlServer.Management.Smo.Login -ArgumentList $ServerObject,$LoginName

Posted in Uncategorized | Leave a comment

Drop login Powershell SMO

For some reason, Powershell is what I’m blogging about these days.  Maybe it is rare to use it in this way, so there is not much information out there.  So I want to be a help to others who need the answers.

I developed a process to drop temporary logins, but discovered a problem recently.  It could not drop the login because of the following error:  Detailed error:Could not drop login ‘LoginName’ as the user is currently logged in.  The individual using the login apparently doesn’t close their SSMS.

There doesn’t seem to be a streamlined way in SMO to drop all connections for that specific login.  But this blog post showed me the EnumProcesses() function, which allowed me to kill the connections for that login.

Without automating anything, here are the simple commands.

Search for connections/processes with login ‘cat’
$ServerObject.EnumProcesses() | Where-Object {$_.Login -eq “cat”}

SMOProcesses

Kill connections manually from above picture
$ServerObject.KillProcess(54);
$ServerObject.KillProcess(68);

For the automated piece, a typical foreach loop is fine

$SqlLoginName = “cat”;
$ConnectionsToDrop = $ServerObject.EnumProcesses() | Where-Object {$_.Login -eq $SqlLoginName};
foreach($ConnectionToDrop in $ConnectionsToDrop)
{  $ServerObject.KillProcess($ConnectionToDrop.Spid); }

Posted in Uncategorized | Leave a comment