Page Comparison

TRUNCATE TABLE Contexts;
TRUNCATE TABLE ContextLogs;
TRUNCATE TABLE ContextIndices;
TRUNCATE TABLE PerformanceRecords;

DBCC INDEXDEFRAG(0, 'Contexts')
DBCC INDEXDEFRAG(0, 'ContextIndices')
DBCC INDEXDEFRAG(0, 'ContextLogs')
DBCC INDEXDEFRAG(0, 'PerformanceRecords')

DBCC SHRINKDATABASE(0, 10) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF)

<#
.SYNOPSIS 
    Performs maintenance activities for a Connect database

.DESCRIPTION
    This runbook performs maintenance activities for a Connect database.

.PARAMETER SqlServer
    Name of the SqlServer

.PARAMETER SqlServerPort
    Port of the SqlServer (Default: 1433)

.PARAMETER Database
    Name of the database
    
.PARAMETER CredentialName
    Name of the Automation PowerShell credential setting from the Automation asset store. 
    This setting stores the username and password for the SQL Azure server
#>
param(
    [parameter(Mandatory=$True)]
    [string] $SqlServer,

    [parameter(Mandatory=$False)]
    [int] $SqlServerPort = 1433,

    [parameter(Mandatory=$True)]
    [string] $Database,
    
    [parameter(Mandatory=$True)]
    [string] $CredentialName
)

$TimestampFormat = "yyyy-MM-dd hhh:mm:ss"

# Get the stored username and password from the Automation credential
$SqlCredential = Get-AutomationPSCredential -Name $CredentialName
if ($SqlCredential -eq $null)
{
    throw "Could not retrieve '$CredentialName' credential asset. Check that you created this first in the Automation service."
}
     
$SqlUsername = $SqlCredential.UserName 
$SqlPassword = $SqlCredential.GetNetworkCredential().Password
$SqlConnectionString = "Server=tcp:$SqlServer,$SqlServerPort;Database=$Database;User ID=$SqlUsername;Password=$SqlPassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

# Create the list of SQL commands to execute
$Commands = @(
    "DBCC SHRINKDATABASE(0, 10) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF)",
    "DBCC INDEXDEFRAG(0, 'ConnectObjects')",
    "DBCC INDEXDEFRAG(0, 'ConnectObjectsHistory')",
    "DBCC INDEXDEFRAG(0, 'ContextIndices')",
    "DBCC INDEXDEFRAG(0, 'ContextLogs')",
    "DBCC INDEXDEFRAG(0, 'Contexts')",
    "DBCC INDEXDEFRAG(0, 'PerformanceRecords')",
    "DBCC INDEXDEFRAG(0, 'ScenarioRegistrations')",
    "DBCC INDEXDEFRAG(0, 'SecureStoreItems')"
)

# Execute SQL commands from list
Try {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($SqlConnectionString)
    $SqlConnection.Open()

    foreach ($Command in $Commands) {
        Write-Output "$(Get-Date -Format "$TimestampFormat") Executing SQL command: $Command"

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Command
        $SqlCommand.CommandTimeout=1500

        Try
        {
            $DataSet = New-Object System.Data.DataSet
            $DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCommand)
            [void] $DataAdapter.fill($DataSet)

            foreach ($DataTable in $DataSet.Tables) {                   
                $DataTable | Format-Table -RepeatHeader
            }

            Write-Output "$(Get-Date -Format "$TimestampFormat") Successful."
            Write-Output ""
        }
        Catch
        {
            Write-Error "$(Get-Date -Format "$TimestampFormat") SQL command '$Command' failed."
        }

        Write-Output ""
    }
}
finally {
    $SqlConnection.Close()
}

Write-Output "Finished."