Thursday, October 31, 2013

Powershell Script to get RAM information

Powershell makes it easy to get information from remote servers.

Below is the query to "calculate RAM using powershell" of all the remote servers you are connected with-

Step1- Create the list of all the servers in a notepad with name "serverlist.txt". I have saved this file on location "D:\Powershell" you can change it with your own location.

Step2- Execute below script-

$servers = Get-Content "D:\PSScripts\bstServerlist.txt"

$SystemInfo = "D:\PSScripts\bstSystemInfo.txt"
$ErrorInfo = "D:\PSScripts\bstErrorInfo.txt"

FOREACH($server in $servers)
$System = gwmi -computer $server -class win32_computersystem -ErrorVariable MyError -ErrorAction Silentlycontinue

$Ram = [math]::round($System.Totalphysicalmemory/(1024*1024*1024),2)
write-output "$server - $Ram " | out-file -filepath $SystemInfo -append

You can also save the script in a notepad and save it with extension ".ps1". When you will execute the file RAM information for all the servers in the server list will store in the RAMInformation file.

robocopy data transfer for only new updates

Every DBA knows about of these asks when we need to transfer the updates and not the complete data every time. eg Logshipping transaction logs need to be copied between two cluster. How to achieve this goal.

robocopy \\SourceServer\Sourcefolder \\DestinationServer\Destinationfolder /Z /S /MIR

Switch /MIR is to apply only new updates.
Switch /S is to apply all the file levels -recursive in the folder.

Most of the readers of this blog also read "robocopy data transfer is very sluggish" to understand the use of /Z in this scenario so linking them here:
robocopy data transfer is very sluggish
Share your feedback which is very important to make the blogs more informative.

robocopy data transfer is very sluggish

We often see DBAs talking that the robocopy data transfer is very sluggish.

Recently I was involved in a project where 14 TB data was being transferred. My DBA informed me that the data transfer rate is just 325 KB/sec. With this rate of transfer it would take 1.4 years to transfer 14 TB of data. There was an absolute need fix either n\w design or robocopy. I chose to review robocopy command.

My DBA was using command with /Z option and without any /MT multi threading. The original command looked like as follows:

robocopy \\SourceServer\Sourcefolder \\DestinationServer\Destinationfolder /Z

I recommended following changes and confirm the speed

robocopy \\SourceServer\Sourcefolder \\DestinationServer\Destinationfolder /MT:64
/Z actually slows the speed a lot. This is absolutely great option to avoid any data discrepancy but I opted not to use this option and in case of any failures, the copy will be retried.

The speed was 595 MB/sec after this change. The copy time decreased from 1.4 years to whopping 6.8 hours. I am completely ok to retry this if any bit transfer is flipped because of n\w issue and causes corruption.

Tuesday, October 29, 2013

The service was started and then stopped

Sql server agent service issue: My junior DBA met me on an interesting issue. Sql service account is not able to start the Sql server agent whereas Sql server service is running. When he tries to start sql agent service then he gets following issue:
“The request failed or the service didn’t respond in a timely fashion. Consult the event log or other application error logs for details.”
“The service was started and then stopped”
Let’s look at the errorlogs from I:\MSSQL10.MSSQLSERVER\MSSQL\LOG. Following error is observed:
Here is the actual problem: “The service account phx\_ctpfort doesn't have perms to start Eventlog service.”
Lets look at the permsisions:
  1. 1. Run -> cmd -> rsop.msc and press enter.
  2. 2. Click on Windows settings.
  3. 3. Click on System services.
  4. 4. Click the properties on Windows event log.
  5. 5. Click on View security to look for the service account under which the sql service is running.

We need to have it added there. Once added, the problem was resolved.
Please share your feedback and let me know if the blog was useful.

Monday, October 28, 2013

SCOM Powershell Queries

Recently, I was thrown in a drill where SCOM health needs to be verified.

"What is SCOM": SCOM is a server/client model that monitors the server health. Server heath can be categorized on multiple criteria. These criteria could be written in MP
(management pack) and then the servers are monitored on the basis of these parameters.
It would have been impossible for me to monitor 800+ servers without SCOM powershell commands.

Following are some commands where servers could be categorized in three
different states. The powershell command is also written against each and every category.

  1. # of Servers where SCOM HeartBeat is Healthy get-scomagent | Where-Object {$_.healthstate -eq "success"} | Measure
  2. # of Servers where SCOM Heartbeat has Issues get-scomagent | Where-Object {$_.healthstate -eq "uninitialized"} | Measure
  3. # of Servers which are not healthy in SCOM get-scomagent | Where-Object {$_.healthstate -eq "Error"} | Measure

Please share your feedback which help me improve my articles.