MSSQLManagementPowerShell.psm1
|
<#
.SYNOPSIS A simple cmdlet to retrieve SQL Agent Job logs #> function Get-SQLAgentJobLogs { [CmdletBinding(SupportsShouldProcess=$false,DefaultParameterSetName="ByServerInstance")] Param( [Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true,ParameterSetName="ByServerInstance",HelpMessage="Name of server instance to connect to")] [String]$ServerInstance, [Parameter(Mandatory=$false,Position=1,ValueFromPipeline=$true,ParameterSetName="ByServerInstance",HelpMessage="How many days back to display")] [Int32]$DaysBack = 1, [Parameter(Mandatory=$false,Position=1,ValueFromPipeline=$true,ParameterSetName="ByServerInstance",HelpMessage="ErrorLevel less then to display")] [Int32]$ErrorLevel = 3 ) Begin { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $DaysBackNegative = -1 * $DaysBack } Process { $sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance $jobServer = $sqlServer.JobServer; return $jobServer.ReadErrorLog() | where { ($_.ErrorLevel -lt $ErrorLevel) -and ($_.LogDate -ge $(Get-Date).AddDays($DaysBackNegative)) } } End { #Put end here } } <# .SYNOPSIS A simple cmdlet to retrieve SQL Agent Job logs #> function Get-SQLAgentJobStatus { [CmdletBinding(SupportsShouldProcess=$false,DefaultParameterSetName="ByServerInstance")] Param( [Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true,ParameterSetName="ByServerInstance",HelpMessage="Name of server instance to connect to")] [String]$ServerInstance, [Parameter(Mandatory=$false,HelpMessage="Show disabled jobs too, defaults to only showing enabled jobs")] [Switch] $ShowDisabledJobs, [Parameter(Mandatory=$false,HelpMessage="Show only failed")] [Switch] $ShowOnlyFailed ) Begin { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null } Process { $sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance $jobServer = $sqlServer.JobServer; $resutls = $jobServer.Jobs | Select-Object -Property Name,IsEnabled,CurrentRunStatus,LastRunOutcome,LastRunDate,NextRunDate,Category,HasSchedule # | ft if ($ShowOnlyFailed) { $resutls = $resutls | Where-Object -Property LastRunOutcome -EQ -Value "Failed" } if ($ShowDisabledJobs) { return $results } else { return $results | Where-Object -Property IsEnabled -EQ -Value "True" } } End { #Put end here } } <# .SYNOPSIS A simple cmdlet to retrieve SQL Agent Job logs #> function Get-SQLLogs { [CmdletBinding(SupportsShouldProcess=$false,DefaultParameterSetName="ByServerInstance")] Param( [Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true,ParameterSetName="ByServerInstance",HelpMessage="Name of server instance to connect to.")] [String]$ServerInstance, [Parameter(Mandatory=$false,Position=1,ValueFromPipeline=$true,ParameterSetName="ByServerInstance",HelpMessage="How many days back to display, defaults to 1.")] [Int32]$DaysBack = 1 ) Begin { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $DaysBackNegative = -1 * $DaysBack } Process { $sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") $ServerInstance $logs = $sqlserver.ReadErrorLog() return $logs | Where-Object -Property LogDate -GE -Value $((Get-Date -Hour 0 -Minute 0 -Second 0).AddDays($DaysBackNegative)) } End { #Put end here } } <# ------------------------------------------------------------------------------------------------------- Marnie Ross Jan 2, 2015 This script iterates through all the indexes in all the tables of a database and based on the level of fragmentation of an index, performs on of the following: If the index fragmentation is less than 5%, it does nothing If the index fragmentation is between 5% and 30%, it reorganizes it If the index fragmentation is over 30%, it rebuilds it ------------------------------------------------------------------------------------------------------- #> function Optimize-SQLIndex { [CmdletBinding(SupportsShouldProcess=$false,DefaultParameterSetName="example")] Param( [Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true,ParameterSetName="example",HelpMessage="The Server instance to check.")] [string] $ServerInstance, [Parameter(Mandatory=$true,Position=1,ValueFromPipeline=$false,ParameterSetName="example",HelpMessage="The database to check")] [string] $Database ) Begin { #Put beginning stuff here } Process { [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null $server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance $targetDB = $server.Databases[$Database] foreach ($table in $targetDB.Tables) { foreach($index in $table.Indexes) { $fragmentation = $index.EnumFragmentation() $averageFragmentation = $fragmentation.Rows[0].AverageFragmentation if ($averageFragmentation -lt .05) { Write-Verbose -Message "No fragmentation...$table" -Verbose continue } if($averageFragmentation -ge .05 -and $averageFragmentation -lt .3) { Write-Verbose -Message "Reorganization...$table" -Verbose $index.Reorganize() continue } Write-Verbose -Message "Rebuild...$table" -Verbose if ($PSCmdlet.ShouldProcess("Rebuild...$table")) { $index.Rebuild() } } } } End { #Put end here } } Export-ModuleMember -Function "Get-SQLLogs", "Get-SQLAgentJobStatus", "Get-SQLAgentJobLogs", "Optimize-SQLIndex" |