commands

^

Remove-DbaDbTableData

Author Adam Lancaster https://github.com/lancasteradam
Availability Windows, Linux, macOS

 

Synopsis

Removes table data using a batch technique. Databases in both on-prem SQL Server and Azure SQL Database are supported.

Description

This command does a batch delete of table data using the technique described by Aaron Bertrand here: https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes. The main goal of this command is to ensure that the log file size is controlled while deleting data. This command can be used for doing both very large deletes or small deletes. Foreign keys are not temporarily removed, so the caller needs to perform deletes in the correct order with dependent tables or enable cascading deletes. When a database is using the full or bulk_logged recovery model this command will take log backups at the end of each batch if the -LogBackupPath or -AzureBaseUrl param is specified. If the database is using the simple recovery model then CHECKPOINTs will be performed for on-prem SQL Server databases. The object returned will contain metadata about the batch deletion process including the log backup details.

There are two categories of use cases for this command:

  1. Simplistic deletions from a table. In this scenario use the -Table and -BatchSize parameters. The DELETE statement is auto-generated by the this command.
  2. Complex deletions based on a join, where clause, or an order by clause. In this scenario use the -DeleteSql parameter to specify the DELETE statement.

Syntax

Remove-DbaDbTableData
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-BatchSize] <Int32>]
    [[-Table] <String>]
    [[-DeleteSql] <String>]
    [[-LogBackupPath] <String>]
    [[-LogBackupTimeStampFormat] <String>]
    [[-AzureBaseUrl] <String[]>]
    [[-AzureCredential] <String>]
    [[-InputObject] <Object[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Remove-DbaDbTableData -SqlInstance localhost -Database TestDb -Table dbo.Test -BatchSize 1000000 -LogBackupPath E:\LogBackups -Confirm:$false

Removes all data from the dbo.Test table in the TestDb database on the local SQL instance. The deletes are dones in batches of 1000000 rows each and the log backups are written to E:\LogBackups.

Example: 2
PS C:\> Remove-DbaDbTableData -SqlInstance localhost -Database TestDb -DeleteSql "DELETE TOP (1000000) deleteFromTable FROM dbo.Test deleteFromTable LEFT JOIN dbo.Test2 b ON deleteFromTable.Id = b.Id"

-LogBackupPath E:\LogBackups -Confirm:$false
Removes data from the dbo.Test table in the TestDb database on the local SQL instance. When specifying -DeleteSql the DELETE statement needs to specify the TOP (N) clause. In this example the deletes
are done in batches of 1000000 rows each and the log backups are written to E:\LogBackups.

Example: 3
PS C:\> Remove-DbaDbTableData -SqlInstance localhost -Database TestDb -Table dbo.Test -DeleteSql "WITH ToDelete AS (SELECT TOP (1000000) Id FROM dbo.Test ORDER BY Id DESC;) DELETE FROM ToDelete;"

-LogBackupPath E:\LogBackups -Confirm:$false
Removes data from the dbo.Test table based on the DELETE statement specified in the -DeleteSql. The deletes occur in the TestDb database on the local SQL instance. The deletes are dones in batches of
1000000 rows each and the log backups are written to E:\LogBackups.

Example: 4
PS C:\> Get-DbaDatabase -SqlInstance localhost -Database TestDb1, TestDb2  | Remove-DbaDbTableData -Table dbo.Test -BatchSize 1000000 -LogBackupPath E:\LogBackups -Confirm:$false

Removes data from the dbo.Test table in the TestDb1 and TestDb2 databases on the local SQL instance. The deletes are dones in batches of 1000000 rows each and the log backups are written to
E:\LogBackups.

Example: 5
PS C:\> $server, $server2 | Remove-DbaDbTableData -Database TestDb -Table dbo.Test -BatchSize 1000000 -LogBackupPath E:\LogBackups -Confirm:$false

Removes data from the dbo.Test table in the TestDb database on the SQL instances represented by $server and $server2. The deletes are dones in batches of 1000000 rows each and the log backups are
written to E:\LogBackups.

Example: 6
PS C:\> $server = Connect-DbaInstance -ConnectionString "Data Source=TCP:yourserver.database.windows.net,1433;MultipleActiveResultSets=False;Connect

Timeout=30;Encrypt=True;TrustServerCertificate=False;User Id=dbuser;Password=strongpassword;Database=TestDb"
Remove-DbaDbTableData -SqlInstance $server -Database TestDb -Table dbo.Test -BatchSize 1000000 -Confirm:$false
Removes data from the dbo.Test table in the TestDb database on the Azure SQL server yourserver.database.windows.net. The deletes are dones in batches of 1000000 rows. Log backups are managed by Azure
SQL. Note: for Azure SQL databases error 40552 could occur for large batch deletions:
https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues#error-40552-the-session-has-been-terminated-because-of-excessive-transaction-log-space-usage

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

Alias
Required False
Pipeline false
Default Value
-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-Database

The database(s) to process. This list is auto-populated from the server. If unspecified, all user databases will be processed.

Alias
Required False
Pipeline false
Default Value
-BatchSize

The number of rows to delete per batch. This param is defaulted to 100000 and limited to a value between 1 and 1000000000 (1 billion). This param can only be used with the -Table param. If -DeleteSql is used the TOP (N) clause must be specified in the SQL DELETE string. Note: for Azure SQL databases error 40552 could occur for large batch deletions: https://docs.microsoft.com/en-us/azure/azure-sql/database/troubleshoot-common-errors-issues#error-40552-the-session-has-been-terminated-because-of-excessive-transaction-log-space-usage

Alias
Required False
Pipeline false
Default Value 100000
-Table

The name of the table that data should be deleted. This param is required except when -DeleteSql is specified. When this param is used the -BatchSize param may also be used (or its default value).

Alias
Required False
Pipeline false
Default Value
-DeleteSql

A SQL DELETE statement to be used in the command's loop for more advanced scenarios such as deleting based on a join, using a where clause, or using an order by clause (or a combination of all of those). It is required that the DELETE statement include the TOP (N) clause. See the example below. This param may be used instead of -Table and -BatchSize.

Alias
Required False
Pipeline false
Default Value
-LogBackupPath

The directory to store the log backups. This command creates log backups when the database is using the full or bulk_logged recovery models and is an on-prem SQL server instance. If this param is not provided the command will not take log backups. This directory should be writeable by the SQL Server service account.

Alias
Required False
Pipeline false
Default Value
-LogBackupTimeStampFormat

By default the command timestamps the log backup files using the format yyyyMMddHHmm. The timestamp format should be defined using the Get-Date formats, because illegal formats will cause an error to be thrown.

Alias
Required False
Pipeline false
Default Value
-AzureBaseUrl

Used for log backups. See https://dbatools.io/Backup-DbaDatabase for information on this parameter. This function invokes Backup-DbaDatabase with -AzureBaseUrl if it is provided.

Alias
Required False
Pipeline false
Default Value
-AzureCredential

Used for log backups. See https://dbatools.io/Backup-DbaDatabase for information on this parameter. This function invokes Backup-DbaDatabase with -AzureCredential if it is provided.

Alias
Required False
Pipeline false
Default Value
-InputObject

Enables piped input of Microsoft.SqlServer.Management.Smo.Database, Microsoft.SqlServer.Management.Smo.Server, and Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter objects.

Alias
Required False
Pipeline true (ByValue)
Default Value
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts for confirmation before executing any data modification operations.

Alias cf
Required False
Pipeline false
Default Value

 

Want to see the source code for this command? Check out Remove-DbaDbTableData on GitHub.
Want to see the Bill Of Health for this command? Check out Remove-DbaDbTableData.