Datenbankwartung - Microsoft SQL Server / Azure SQL (Connect 2024)

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

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

DECLARE @DeleteBefore DATETIME = CAST('2000-12-31T00:00:00' AS DATETIME);

DELETE FROM Contexts WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore);
DELETE FROM ContextIndices WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore);
DELETE FROM ContextLogs WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore);
DELETE FROM Contexts WHERE FlowContextId IS NULL AND CreateTime < @DeleteBefore;
DELETE FROM PerformanceRecords WHERE [Time] < @DeleteBefore;

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

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