SQL Server Administration by Powershell (Part 1)

The estimated reading time for this post is 3 minutes

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)

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

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

 

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz