Versions Compared

Version Old Version 4 New Version Current
Changes made by

Thomas Erdösi

Thomas Erdösi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

DELETE FROM Contexts WHERE ContextType = 'ElementContext' AND FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextIdContextType IS= NULL'FlowContext' AND CreateTime < @DeleteBefore);
DELETE FROM ContextIndices WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextIdContextType IS= NULL'FlowContext' AND CreateTime < @DeleteBefore);
DELETE FROM ContextLogs WHERE FlowContextId IN (SELECT Id FROM Contexts WHERE FlowContextIdContextType IS= NULL'FlowContext' AND CreateTime < @DeleteBefore);
DELETE FROM Contexts WHERE FlowContextIdContextType IS= NULL'FlowContext' 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')

Sollen alle Kontexte und Leistungsdatensätze gelöschte werden, kann dieser Vorgang durch die Verwendung von TRUNCATE beschleunigt werden:

Code Block
languagesql
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')

Verkleinern der Datenbank

Das Löschen einer großen Menge an Daten führt nicht unbedingt dazu, dass auch die Größe der Datenbank abnimmt. In diesem Fall empfiehlt es sich, die Datenbank anzuweisen, den nicht mehr benötigten Platz wieder freizugeben. Dies lässt sich mit folgendem SQL-Skript erreichen:

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

Der Wert 10 bezieht sich hier auf den Prozentsatz an unbenutzten Speicherplatz, den die Datenbank weiterhin belegen darf. Dies ermöglicht das Einfügen neuer Daten, ohne dass zusätzlicher Speicher reserviert werden muss. Nachdem die Datenbank verkleinert wurde, kann es sinnvoll sein, eine Reorganisation der Indizes durchzuführen.

Planen Sie die Reorganisation von Indizes bei Verwendung von Azure SQL Database

...

Code Block
languagepowershell
<#
.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."

...