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

#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

Listing Installed Hotfixes and Service Packs

#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

Listing Running/Blocking Processes

#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

Checking Disk Space Usage

#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 }

Checking Index Fragmentation

#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

Reorganizing / Rebuilding Index

#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"

Running DBCC Commands

#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)

[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

#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

 

 

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