SQL Server Administration by Powershell (Part 1)

Powershell is a new modular command-line tool from Microsoft, by using this tool you are able to perform whatever you can do by GUI. most of Microsoft products have Powershell module which you may need to import it manually.

In this blog, I am going to share some of SQL Server administrative tasks that can be accomplished  using PowerShell. PowerShell can help you to automate a lot of the repetitive, tedious, and mundane tasks that take many clicks to accomplish.

This blog post structure is by section and each section contains a specific Powershell script for specific task.

SQL Server Instance Inventory

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name 
$instanceName = "SQLInstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. 
Server -ArgumentList $instanceName

$folder = "C:Temp" 
$date = Get-Date -Format "yyyy-MM-dd_hmmtt" 
$filename = "$($instanceName)_Inventory_$($date).csv" 
$path = Join-Path $folder $filename

$server | Get-Member | Where-Object Name -ne "SystemMessages" | Where-Object MemberType -eq "Property" | Select Name, @{Name="Value";Expression={$server.($_.Name)}} | Export-Csv -Path $path -NoTypeInformation 

[/codesyntax]

Listing Installed Hotfixes and Service Packs

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking
#replace this with your instance name 
$instanceName = "SQLInstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. 
Server -ArgumentList $instanceName

$server.Information.VersionString

$server.Information.ProductLevel

Get-Hotfix

[/codesyntax]

Listing Running/Blocking Processes

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name 
$instanceName = "SQLInstance" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. Server -ArgumentList $instanceName 

#Running Processes
$server.EnumProcesses() | Select Name, Spid, Command, Status, Login, Database, BlockingSpid | Format-Table -AutoSize

#Blocking Processes
$server.EnumProcesses() | Where-Object BlockingSpid -ne 0 | Select Name, Spid, Command, Status, Login, Database, BlockingSpid | Format-Table -AutoSize 

[/codesyntax]

Checking Disk Space Usage

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking 

$instances = "SQL01 `nSQL02" 

$query = "SELECT @@SERVERNAME 'SERVERNAME', @@VERSION 'VERSION'" 

$databasename = "master" 

$instances | ForEach-Object {   $server = New-Object -TypeName Microsoft.SqlServer.Management. Smo.Server -ArgumentList $_   Invoke-Sqlcmd -ServerInstance $_ -Database $databasename -Query $query }

[/codesyntax]

Checking Index Fragmentation

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking;

#replace this with your instance name 
$instanceName = "SQLInstance" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. Server -ArgumentList $instanceName 

$databasename = "UserDatabase" 
$database = $server.Databases[$databasename]
$tableName = "Transactions" 
$schemaName = "dbo" 

$table = $database.Tables | Where Schema -Like $schemaName | Where Name -Like $tableName

$table.Indexes | Foreach {   $_.EnumFragmentation() | Select Index_Name, @{Name="Value";Expression={($_. AverageFragmentation).ToString("0.00")}} } | Format-Table -AutoSize

[/codesyntax]

Reorganizing / Rebuilding Index

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name 
$instanceName = "SQLInstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. 
Server -ArgumentList $instanceName

$VerbosePreference = "Continue" 
$databasename = "UserDatabase" 
$database = $server.Databases[$databasename]

$tableName = "Transactions" 
$schemaName = "dbo" 

$table = $database.Tables | Where Schema -Like $schemaName | Where Name -Like $tableName

$table.Indexes | ForEach-Object {   $_.EnumFragmentation() |   
ForEach-Object {        $item = $_       #reorganize if 10 and 30% fragmentation        if($item.AverageFragmentation -ge  10 -and `           $item.AverageFragmentation -le 30  -and `           $item.Pages -ge 1000)        {           Write-Verbose "Reorganizing $index.Name ... "           $index.Reorganize()        }        #rebuild if more than 30%        elseif ($item.AverageFragmentation -gt 30 -and `                $item.Pages -ge 1000)        {           Write-Verbose "Rebuilding $index.Name ... "           $index.Rebuild()        }   } }
$VerbosePreference = "SilentlyContinue"

[/codesyntax]

Running DBCC Commands

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name 
$instanceName = "SQLInstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. Server -ArgumentList $instanceName

$databasename = "UserDatabase" 
$database = $server.Databases[$databasename] 
#RepairType Values: AllowDataLost, Fast, None, Rebuild 
$database.CheckTables([Microsoft.SqlServer.Management.Smo. RepairType]::None)

[/codesyntax]

[info]Not all DBCC commands are wrapped in SMO methods. Some of the available methods on a database level are CheckAllocations, CheckCatalog and CheckTables[/info]

Listing SQL Server Jobs

[codesyntax lang=”powershell”]

#import SQL Server module 
Import-Module SQLPS -DisableNameChecking

#replace this with your instance name 
$instanceName = "SQLINstance" 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo. Server -ArgumentList $instanceName 

#List all Jobs
$jobs=$server.JobServer.Jobs $jobs | Select Name, OwnerLoginName, LastRunDate, LastRunOutcome | Sort -Property Name | Format-Table -AutoSize

#List all Failed Jobs
$jobs=$server.JobServer.Jobs $jobs | Where LastRunOutcome -Like "Failed" | Select Name, OwnerLoginName, LastRunDate, LastRunOutcome |  Format-Table -AutoSize

[/codesyntax]

 

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan