PowerShell script for recovering a service

Posted on by
Reply

First of all; Happy new year!

This script I’m about to show you is more of a workaround than a solution. Sometimes the recovery function for services is not enough to recover a service. I’ve found that in a number of occurrences the service doesn’t start successfully and stays in status “starting”. Windows doesn’t pick this up and the service stays in status “starting” until some third party monitoring software or someone else discovers the failed start.

Now as I wrote earlier this script is more of a workaround than a solution, but it has helped me more than once to serve as a quick-fix and buys some more time to get to the root cause of the issue.

# Check service status
$SrvName = "Service name"
# Enter the name of the service. E.g. "SQL Server Reporting Services (MSSQLSERVER)"

$ServiceStatus = Get-Service $SrvName

# Execute Start-Service or no action based on status of the service
if ( $ServiceStatus.status –eq "Running" )
{
Echo "No Action - The service is running"
}
ELSE
{
Echo "The service has status starting, stopping or stopped."
Echo "Attempting to start the service."
Start-Service -Name $SrvName
}

MSSQL: Database in recovery, what to do now?

Posted on by
Reply

You have a large database which is currently in recovery, and you have no idea how long it will take. I’ve been there more than once, and this has helped me out:

SELECT der.session_id, der.command, der.status, der.percent_complete, *
FROM sys.dm_exec_requests AS der

Also, I found a nice post the other day with a query which displays time remaining on a restore. Could come in handy! Check it out here: How much longer will the SQL Server database restore take

Great editor for PowerShell scripting

Posted on by
Reply

Hello!

I’ve posted some scripts and other (hopefully) useful tips and tricks for PowerShell scripting. However, I’ve never told you about PowerGUI. Microsofts editor is very simple and is just fine, but PowerGUI takes PowerShell scripting to a whole new level! It’s free, easy to use and there are a lot of youtube videos out there showing you how to utilize it’s best features. You can get it here.

Also; the PowerGUI site also has a big library of PowerShell scripts for you to use.

Clean up your log directories with PowerShell

Posted on by
Reply

Well now! It’s been a long time since the last post and I’m sorry. Things have been quite hectic!

Today I’d like to share with you a script for cleaning up your log directories. Very practical if you don’t have the need for log files older than xx days.
As usual this is a small script, but it can be incorporated with other existing scripts, or you could build more logic to delete files with certain file extensions or the like.


## Set UNC path. This is the folder which will be cleaned by the script.
$folderPathRemoteFolder = \\ServerName\c$\foldername

## Keep files for x days. The script will delete all files older than the number of days configured below.
$KeepFiles = "7"

#Removing files older than x days
Get-ChildItem $folderPathRemoteFolder -recurse | ?{!$_.PSIsContainer -and ($_.LastWriteTime -lt (Get-Date).adddays("-" + "$KeepFiles"))} | Remove-Item -force

MSSQL – Check fragmentation on indexes

Posted on by
Reply

Today I’d like to share with you a simple script to check fragmentation on indexes.
You can of course edit the fragmentation percentages from 10 and 40 to something else if you’d like to categorize them differently.

SELECT
OBJECT_NAME(indstat.object_id, indstat.database_id) AS obj_name,
QUOTENAME(sysind.name) [index_name],
CASE
WHEN avg_fragmentation_in_percent < 10 THEN 'LOW'
WHEN avg_fragmentation_in_percent < 40 THEN 'MEDIUM'
ELSE 'HIGH'
END as frag_level,
indstat.*

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
AS indstat

INNER JOIN sys.indexes sysind ON indstat.object_id = sysind.object_id AND
indstat.index_id = sysind.index_id

ORDER BY avg_fragmentation_in_percent DESC

Send e-mails using SMTP with PowerShell

Posted on by
Reply

I’ve used this on several occasions to send log files or other result files by email. It’s a fairly simple script and very simple to implement. I hope you find it useful!


## Mail from "Name "
$MailFromAdr = "Server1 "

## Mail to. You can add as many senders as you'd like.
## Using a mailinglist is recommended, that way you wouldn't need to
## edit the script for every change.
$MailToAdr1 = "mailinglist@blogofchris.com"
$MailToAdr2 = "name@blogofchris.com"

## Mail Subject.
$MailSubject = "Log"

## Mail Body - Specify what you'd like in the mail body.
$MailBody = "The log file is attached. This script is scheduled to run every weekday at 6:00 on server1."

## Mail server - SMTP
$MailServer = "serversmtp"

## SMTP Credentials - If required
$MailUserName = "username"
$MailPassword = "password"

## Specify filename. See my previous post about adding logging to your
## PowerShell scripts.
## http://www.blogofchris.com/powershell-add-logging-to-your-scripts/
## In that example if you'd like to send the logfile, you just set drop
## out the $FileName = part, and set the $Att to
## be new-object Net.Mail.Attachment($LogFile)
$FileName = "C:\Logs\Logfile.log"
$Mail = New-Object System.Net.Mail.MailMessage
$Att = new-object Net.Mail.Attachment($FileName)
$Mail.From = "$MailFromAdr"
$Mail.To.Add("$MailToAdr1")
$Mail.To.Add("$MailToAdr2")

## Here I've added a timestamp to the subject in order to get unique
## and more informative subjects.
## You can remove the + " $TimeStamp" part if you don't want that.
$Mail.Subject = "$MailSubject" + " $TimeStamp"
$Mail.Body = "$MailBody"
$Mail.Attachments.Add($Att)
$SMTP = New-Object System.Net.Mail.SmtpClient("$MailServer")
$SMTP.Credentials = New-Object System.Net.NetworkCredential("$MailUserName","$MailPassword")
$SMTP.Send($Mail)
$Att.Dispose()

Specify port in SQL Server Management Studio

Posted on by
1

The first time I needed to specify port was when I had to connect through a firewall, and no ports were open except for the SQL port which was set to a non-standard port (IOW: not 1433).
I searched for what felt like hours on microsofts sites and google to no avail. I tried colon, space and everything I could think of to specify port number but nothing worked.

I ended up asking a colleague of mine who has worked with MS SQL for many, many years, and it turned out to be the easiest thing in the world! You just separate the host name or IP (and instance if not installed as default) and the port number with a comma. I’ve never used a comma before so I never thought to try it.

Example if you want to connect your instance with IP 1.2.3.4 and name myinstance on port 61195 you type 1.2.3.4\myinstance,61195.
If DNS is available you could just type MyHostName\MyInstance,61195.

This didn’t end up being the most interesting post in the world, but my hope is that someone else searching for the answer will find it some day :)

MSSQL Performance issues

Posted on by
Reply

I’ve previously posted about deadlocks in MSSQL and figuring out the process creating problems. However; deadlocks is only a tiny bit of what can be bottlenecks in your setup. I found this tool some time ago which could analyze trace files and suggest where your bottlenecks are. It’s called SQL Nexus, is open source and available here.

Another tip would be to run Microsofts best practices analyzer. If you’re not sure if your setup is according to best practices, it can be a real help!
Links for: SQL 2005 and SQL 2008 R2.

PowerShell – Add logging to your scripts

Posted on by
Reply

This isn’t very advanced, but still something which has helped me a lot when troubleshooting powershell scripts refusing to execute correctly.
Especially if it works when you test it manually, but fails with automatic execution…

It will set working directory, so you won’t need to worry about hard coded paths when making your scripts.
It also creates a log directory if it does not already exist and ads a timestamp to the log file name.

Happy PowerShelling :D

#Set working directory
$WorkingDir = Get-Location

#Set logs directory
$LogsDir = "$WorkingDir\Logs\"

#Set timestamp for log file
$TimeStamp = Get-Date -Format "yyyyMMdd_hhmm"

#Determine log file name
$LogFile = "$LogsDir" + $TimeStamp + "_ScriptName.log"

#Check if logs directory exists. If not, create logs directory.
if(test-path $LogsDir)
{ echo "Logs directory exists."
}
else
{
New-Item .\Logs -type Directory
}

#Start logging
Start-Transcript $LogFile

Your script goes here

#Stop logging
Stop-Transcript

Deadlocks in MSSQL

Posted on by
Reply

I’ve worked with MSSQL databases for some years, and deadlock situations are always something which can ruin an otherwise good setup.

Here’s something I found useful in finding the culprit:

First execute

SELECT * FROM sys.sysprocesses WHERE blocked <> 0

Find the process(es) you think create the unwanted deadlock. Find the SQL_HANDLE for that process and execute the following where you change SQL_HANDLE out with the SQL_HANDLE you’ve found.

USE [tempdb]
GO

CREATE TABLE Test
(RowID INT IDENTITY PRIMARY KEY)
GO

CREATE TRIGGER Audit_Test
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SELECT TEXT
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(SQL_HANDLE)
WHERE session_id = @@SPID
END
GO

INSERT INTO Test DEFAULT VALUES;
GO