Thursday 11 February 2016

Not enough storage is available to process this command. (Exception from HRESULT 0x80070008)


When executing a PowerShell script using the invoke-command to remotely access several web servers, I encountered the following error message emanating from one of the servers.


The error message means the quota management of the remote shell is limited the amount of memory allocated to that shell. This allows the system to handle resource more efficiently. To resolve this issue it involves accessing the server locally and manually change the PowerShell MaxMemoryPerShellMB settings directly.

To resolve this error, login locally to the server, open a PowerShell session and run the following. 

sl WSMan:\localhost\Shell

Then check the size of memory allocated by running this

dir | Where-Object {$_.name -eq 'MaxMemoryPerShellMB' }

or the shorter version

get-item MaxMemoryPerShellMB

Below you can see the PowerShell memory setting was set to 100Mb.

I increased this with an extra 100mb set to total 200mb.

Set-Item WSMan:\localhost\Shell\MaxMemoryPerShellMB 200


So now when i run invoke-command remotely, the error did not occur and get the result I wanted.


Sunday 24 January 2016

Setup Error for SSIS installation on Clustered Environment.

  

During an installation of SSIS on a passive clustered node, the SCC was generating the errors below in the setup logs. This was preventing the installation of the SSIS engine on the passive node.

After some investigation, it was discovered there was a previous uninstalled Sql instance that did not appear to be removed cleanly.

The error message was referencing a disk that did not exist anymore on the clustered environment.

Failed to find a cluster group that owned shared disk: R:

Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.4035.0
Error: Action "InvokeSqlSetupDllAction" threw an exception during execution.  Error information reported during run:
  Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Mon Nov 17 17:05:55 2008
     Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
----------------------------------------------------------
Failed to find property "ComputerList" {"SqlComputers", "", ""} in cache
      Source File Name: datastore\clusterinfocollector.cpp
    Compiler Timestamp: Mon Nov 17 17:05:55 2008
         Function Name: ClusterInfoCollector::collectClusterVSInfo
    Source Line Number: 888
    ----------------------------------------------------------
    Failed to detect VS info due to datastore exception.
          Source File Name: datastore\clustergroupsproperties.cpp
        Compiler Timestamp: Mon Nov 17 17:05:55 2008
             Function Name: ClusterGroupScope.SharedDisks
        Source Line Number: 56
        ----------------------------------------------------------
        Failed to find a cluster group that owned shared disk: R:
WinException : 2
        Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.
  Source File Name: datastore\clustergroupsproperties.cpp
Compiler Timestamp: Mon Nov 17 17:05:55 2008
     Function Name: ClusterGroupScope.SharedDisks
Source Line Number: 56







Solution:

The solution was to re-introduce a new disk named R: to the cluster available storage.




Then running SSIS setup again, the installation ran smoothly without error.
Issue resolved.


Saturday 5 December 2015

Non Printable Character Error System Number 1072896760


You may comes across a situation where a application is throwing this error message.


System Number: 1072896760 
Code: 1072896760 
Description:Unknown error. 
Source:Microsoft OLEDB Persistence Provider 


It may not be immediately apparent what the cause of this error is or where to start looking as
its not descriptive to know a place to start. So one technique to check the origin of the error is the database records being pushed to the application.  
This application is built on .Net with a SQL Server back end.

During the troubleshooting phase it was observed the error did not occur with one set of records, though when tested with a different set of records the application would throw the error.

This required some further analysis of the records triggering the error. At first glance the records did not show anything unusual. It is of data type Varchar (100). The characters in the field looked normal with no indication how a Varchar record could cause an application to break.

It was decided to look at the record from a different perspective. Instead look if there was something in the data that could not be viewed normally. I wrote this script to highlight if there was an Ascii value in the record that could not be visible.

Below is this script that highlights any data outside of the normal Ascii range for printable characters.


CREATE TABLE TEST ( COL1 VARCHAR(100));
GO
INSERT INTO TEST VALUES('THIS IS A NONPRINTABLE CHARACTER STRING');

GO
DECLARE @pos int, @string char(100);
SET @pos = 1;
SELECT @string = COL1 FROM TEST
WHILE @pos <= DATALENGTH(@string)
BEGIN
IF ASCII(SUBSTRING(@string, @pos, 1)) <= 31
SELECT ASCII(SUBSTRING(@string, @pos, 1)) , 'NON PRINTABLE CHARACTER FOUND '
ELSE
SELECT ASCII(SUBSTRING(@string, @pos, 1)) , CHAR(ASCII(SUBSTRING(@string, @pos, 1)))

SET @pos = @pos + 1
END;

GO


In this example it was identified that it was a Non Printable Character causing the Web Application to fail.
The application was trying to handle a record from Sql Server that had the Non Printable Character Record Separator RS.
The web application was unable to handle this unexpected data, which caused it to fail and throw the above error message.

To manually generate this Non printable Character you would select the following on your keyboard.
Ctrl  Shift  ^
You can see this symbol represented in text editor NotePad++ as shown below.

(This text below has an Record Separator RS embedded in it).
'EXAMPLE TESTING OF NON PRINTABLE CHARACTER'



This issue was simply resolved at source by manually editing the record, deleting it and entering the data again, removing the unwanted character.

Sunday 23 August 2015

Programming SSIS Package Dataflow Using C# , DB2


If you work in an environment where you maintain large volumes of SSIS packages, this can be a time consuming overhead. 

Especially if your handling 100's of SSIS packages, the need to automate maintenance routines becomes more apparent. 

One of the common work tasks with ETL's is the continual adding of new tables or fields. When your operating in an workplace where this is an daily occurrence you can envisage how this task should be done more efficiently. 

In this program its coded primarily to work with DB2 as source and Sql Server as destination. The SSIS DB2 connections are using an Microsoft® OLEDB Provider for DB2.

Below is the tool user manual and the link to the source code.
The source code is written in C# Forms, .Net Framework 4 and Visual Studio 2010.

The source code is available at https://github.com/brenmcveigh/SSIS-DB2


Saturday 22 August 2015

Trouble Shooting Wait Type PREEMPTIVE_OS_WRITEFILEGATHER


In this real world scenario we encounter the SQL Server Wait type PREEMPTIVE_OS_WRITEFILEGATHER.
During production hours we started to receive alerts about our Web Application hanging. Users where unable to login to the Web App, first they would experience a hanging in the web browser and eventually a timeout error would appear.

The first place to check was the Sql Instance. It was noticed some Sql Server processes where being blocked for a period of a few minutes before they proceeded to complete. We could quickly see a bottleneck of processes gathering on the Sql Server.

The next immediate step to take was to see the Wait Types that where occurring on the Sql Server. When reviewing the Wait types the one that stood out was PREEMPTIVE_OS_WRITEFILEGATHER, as the blocking process was showing up this Wait Type.


We could then quickly identify what was cusing the bottleneck of blocking process. The Sql Server was performing a Transaction log file Autogrowth and it was attempting to Zero extra space on the file. The Transaction Log file size was 40GB and it was set to Autogrow by 10%. A quick additional investigation of the Error Log files showed up the following errors.

There are several suggestions how to prevent this from occurring. Such as improved planning of the expected file size growth, configure the SQL Server to perform Instant File Initialization or even wait for the process to complete, which could take sometime. In this real world scenario the quickest method to remedy the situation was to reduce the expected file growth size for the Transaction log.

The Autogrowth was changed from a % to a fixed growth size of 500MB. When this was set the processes immediately resumed back to normal. The users could access the Web App without the timeouts occurring. The production environment went back to normality.

Thursday 23 July 2015

PowerShell IIS Web Farm Administration


If you work in an environment where your responsible for an IIS Web farm, your role can revolve around dozens of administrative tasks. If your dealing with 10's of web servers in your web farm it can be time consuming to manually maintain and support your servers. Such as configuring application pools, changing web.config settings or creating virtual directories etc.

With the advent of Powershell this has made the tasks faster and more accurate when implementing web server changes.

Below is a series of Powershell scripts I written to automate the tasks of maintaining web servers.



Backup a File on All Web Servers


#Backup a File on All Web Servers 
$Source = "C:\inetpub\xyz.asp"
$BackupFile = "C:\inetpub\zxc.asp-BACKUP"

$servers='MyWebServer01','MyWebServer02','MyWebServer03','MyWebServer04','MyWebServer05'

$Username  = read-Host  "Enter your Windows Login: "
$Password  = read-Host -AsSecureString   "Windows Server Password:"
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass

ForEach ($server in $servers)
{
       invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 
       {
          import-module -Name webadministration
          Copy-Item -Path $Source  -Destination  $BackupFile                             
       }
}


Backup All Web Config Files
# Backs up all the Web config files that have a connection string on the Web Server $BackupFile = "-BACKUP" $Files =  
'C:\inetpub\MyWebSite\MyFolder01\Web.Config'
' C:\inetpub\MyWebSite\MyFolder02\Web.Config'
' C:\inetpub\MyWebSite\MyFolder03\Web.config',
' C:\inetpub\MyWebSite\MyFolder04\Web.config',
' C:\inetpub\MyWebSite\MyFolder05\Web.config',
' C:\inetpub\MyWebSite\MyFolder06\Web.config',
' C:\inetpub\MyWebSite\MyFolder07\Web.config',
' C:\inetpub\MyWebSite\MyFolder08\Web.config'
' C:\inetpub\MyWebSite\MyFolder09\Web.config' 

ForEach ($server in $servers)
{   
  Write-host $Server$BackupFile  
  Copy-Item -Path $server  -Destination  $Server$BackupFile        
}




Change Part of Connection Strings In Web Config Files
$Files =  
'C:\inetpub\MyWebSite\MyFolder01\Web.Config'
' C:\inetpub\MyWebSite\MyFolder02\Web.Config'
' C:\inetpub\MyWebSite\MyFolder03\Web.config',
' C:\inetpub\MyWebSite\MyFolder04\Web.config',
' C:\inetpub\MyWebSite\MyFolder05\Web.config',
' C:\inetpub\MyWebSite\MyFolder06\Web.config',
' C:\inetpub\MyWebSite\MyFolder07\Web.config',
' C:\inetpub\MyWebSite\MyFolder08\Web.config'
' C:\inetpub\MyWebSite\MyFolder09\Web.config' 

  
ForEach ($File in $Files)
{
 write-host 'Processing Server: '   $File 
 (Get-Content -Path $File) | ForEach-Object {$_ -replace 'MyOldSQLINSTANCE, 1234', 'MyNewSQLINSTANCE, 5678' } | Set-Content $File
}
write-host ' Finished'




Get All Web Server Names
$servers = 'MyWebServer01','MyWebServer02','MyWebServer03',' MyWebServer04','MyWebServer05' set-item WSMan:\localhost\Client\TrustedHosts -Value "MyWebServer01, MyWebServer02, MyWebServer03, MyWebServer04, MyWebServer05" set-executionpolicy remotesigned $Username = read-Host "Enter Web Server Login" $Password = read-Host -AsSecureString "Server Password" $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$password ForEach ($server in $servers) { invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 
    {
   
     import-module -Name webadministration
     Get-WebConfiguration -Filter "/system.webServer/httpProtocol/customHeaders/Add/." 
     -Location "C:\Windows\System32\inetsrv\config" | Where-Object { $_.name -eq 
     "X-serverName" } | Select-Object value
                        
    }
}

 

Get WebSite Application Pool
$servers ='MyWebServer01','MyWebServer02','MyWebServer03','MyWebServer04','MyWebServer05' $Username = read-Host "Enter Server Login" $Password = read-Host -AsSecureString "Server Password" $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$Password ForEach ($server in $servers) { invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 
       {
   
         # Load IIS module:
         Import-Module WebAdministration
         # Set a name of the site we want to recycle the pool                  
         $site = "www.myWebSite.com/MyVirtualFolder/"
         # Get pool name by the site name:
         $pool=(Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
         write-host $using:server , $Site,  $pool  -f Cyan                        
       
       }
    }

  

Search File Contents on All Web Servers
set-item WSMan:\localhost\Client\TrustedHosts Value "MyWebServer01, MyWebServer02, MyWebServer03, MyWebServer04, MyWebServer05" set-executionpolicy remotesigned $servers = MyWebServer01','MyWebServer02',' MyWebServer03',' MyWebServer04','MyWebServer05' $Username = read-Host "Enter your Windows Login: " $Password = read-Host -AsSecureString "Windows Server Password:" $file = "C:\inetpub\MyWebsite\MyPhysicalFolder\web.config" #File to search#&gt; $SearchString = "add type" #Text to find in file#&gt; $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$password ForEach ($server in $servers) { invoke-command -ComputerName $server -Credential $Cred -ScriptBlock 
       {
   
        import-module -Name webadministration
        Get-Content -Path $using:file  | WHERE { $_ -Match $using:SearchString }
                        
       }

    }
____

Tuesday 30 June 2015

SSIS Checking log progress


If your SSIS package logs its progress in the table sysssislog, you can track its progress with the following query. 
Its assumed SSIS Logging is using OnPostExecute and OnPreExecute entries to identity the start and stop of the SSIS task.  
This sql query is used for an SSIS package that pulls data from source to destination table. 



WITH
Post AS (
SELECT ID, event, source, starttime, endtime, executionid FROM Msdb.dbo.sysssislog WHERE CONVERT( varchar(10), Starttime, 103 ) =CONVERT(varchar(10), GETDATE() , 103 ) AND EVENT='User:OnPostExecute' 
),
Pre AS (
SELECT ID, event, source, starttime, endtime, executionid FROM Msdb.dbo.sysssislog WHERE CONVERT( varchar(10), Starttime, 103 ) =CONVERT(varchar(10), GETDATE() , 103 ) AND EVENT='User:OnPreExecute' 
)
SELECT Pre.ID, Pre.source AS [TABLE], Pre.starttime , Post.EndTime ,DATEDIFF(MI,Pre.starttime , Post.EndTime ) as Minutes, Pre.executionid
FROM PRE
LEFT JOIN POST ON Pre.source = Post.source AND Pre.executionid = Post.executionid
order by Pre.starttime DESC