主页 / dba / 问题 / 30772
Accepted
Iain Samuel McLean Elder
Asked: 2012-12-20 10:57:14 +0800 CST

为什么我缺少一些输出？

  • 772

存档以避免 ID 耗尽

我们的 OLTP 日志记录系统的一个表中的 ID 即将用完。我的工作是找到一种方法来“归档”整个表和所有引用表中的现有行数据，以便我们可以继续将新数据记录到表中。

我们将数据归档到一个表中的最快方法就是简单地重命名目标表和所有依赖对象——连接表、索引、约束。它速度快，并且保持数据完好无损。为了完成这项工作，我们必须使用旧名称为所有对象创建新的空副本。如果我们在一个事务中完成所有这些操作，插入新数据的存储过程就不会因为缺少表而失败。

尝试使用 PowerShell 解决

我已经将一个 PowerShell 脚本放在一起来生成一个 T-SQL 脚本来执行存档操作。

该脚本没有生成应有的重命名语句，我不明白为什么。

该脚本加载 SMO，设置目标对象的名称，并设置到服务器的仅捕获连接 - 这样我就可以捕获重命名命令以供稍后检查：

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'CLOUDCORP\LOGGING'
$DatabaseName = 'Logging'
$TableName = 'tbDataRequests'
$TableSchemaName = 'Logging'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

接下来它创建一个对象数组，这些对象将通过重命名进行归档。目标表本身、所有引用表以及这些表的所有键和所有索引都将被归档：

$Database = $Server.Databases[$DatabaseName]

$Table = @($Database.Tables[$TableName, $TableSchemaName])

$ReferencingTables = $Database.Tables.ForeignKeys |
    ? { $_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName } |
    % { $_.Parent } | Sort ID | Get-Unique

$TablesToArchive = $Table + $ReferencingTables

$ObjectsToArchive = $TablesToArchive + $TablesToArchive.Indexes + $TablesToArchive.ForeignKeys

接下来是问题部分。对于每个要归档的对象，我尝试捕获一个重命名语句。

$ObjectsToArchive | % { $_.Rename($_.Name + '_archive') }
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text

意外的输出

对我的数据库运行后，该数组$ObjectsToArchive包含 48 个项目。该表达式$objectsToArchive.Name生成如下列表：

tbDataRequests
tbDataRequestPenguinServers
tbDataRequestLegs
tbDataRequestPaidDataRequests
tbDataRequestResponses
tbDataRequestUUIDMappings
tbDataRequestRouteNodes
tbRequestDataRequestQ
tbRequestDataRequests
IX_DataRequests_DataRequestDT
PK_DataRequests
PK_DataRequestPenguinServers
PK_DataRequestLegs
IX_DataRequestPaidDataRequests_PaidDataRequestID
PK_DataRequestPaidDataRequests
IX_DataRequestResponses_ScrapeResponseID
PK_DataRequestResponses
IX_DataRequestUUIDMappings_DataRequestID
PK_DataRequestUUIDMappings
PK_Geo_DataRequestRouteNodes
IX_RequestDataRequestQ_FK_RequestUUIDID
PK_RequestDataRequestQ
IX_RequestDataRequests_DataRequestID
PK_RequestDataRequests
FK_DataRequests_OrderType_OrderTypeID
FK_DataRequests_DatePairs_DatePairID
FK_DataRequests_CustomerCounts_CustomerCountID
FK_DataRequests_Routes_RouteID
FK_DataRequests_DataClients_DataClientID
FK_DataRequests_UserCountries_UserCountryID
FK_DataRequests_Websites_WebsiteID
FK_DataRequestPenguinServers_PenguinServers_JacqiardServerID
FK_DataRequestPenguinServers_DataRequests_DataRequestID
FK_DataRequestLegs_DatePairs_DatePairID
FK_DataRequestLegs_LegTypes_LegTypeID
FK_DataRequestLegs_Routes_RouteID
FK_DataRequestLegs_DataRequests_DataRequestID
FK_DataRequestPaidDataRequests_PaidDataRequests_PaidDataRequestID
FK_DataRequestPaidDataRequests_DataRequests_DataRequestID
FK_DataRequestResponses_DataRequests_DataRequestID
FK_DataRequestResponses_ScrapeResponses_ScrapeResponseID
FK_DataRequestUUIDMappings_DataRequests_DataRequestID
FK_DataRequestUUIDMappings_DataRequestUUIDs_Scrape RequestUUIDID
FK_Geo_DataRequestRouteNodes_DataRequests_DataRequestID
FK_RequestDataRequestQ_RequestUUIDs_RequestUUIDID
FK_RequestDataRequestQ_DataRequests_DataRequestID
FK_RequestDataRequests_Requests_RequestID
FK_RequestDataRequests_DataRequests_RequestID

该数组中的每个对象都应该有一个重命名语句。但是，CapturedSql 属性仅包含 10 个重命名语句。该表达式$RenameCommands | ? { $_ -like '*sp_rename*' }生成如下列表：

EXEC dbo.sp_rename @objname = N'[Logging].[tbDataRequests]', @newname = N'tbDataRequests_archive', @objtype = N'OBJECT'
EXEC sp_rename N'[Logging].[tbDataRequests].[IX_DataRequests_DataRequestDT]', N'IX_DataRequests_DataRequestDT_archive', N'INDEX'
EXEC sp_rename N'[Logging].[tbDataRequests].[PK_DataRequests]', N'PK_DataRequests_archive', N'INDEX'
EXEC sp_rename N'[FK_DataRequests_OrderType_OrderTypeID]', N'FK_DataRequests_OrderType_OrderTypeID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DatePairs_DatePairID]', N'FK_DataRequests_DatePairs_DatePairID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_CustomerCounts_CustomerCountID]', N'FK_DataRequests_CustomerCounts_CustomerCountID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Routes_RouteID]', N'FK_DataRequests_Routes_RouteID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DataClients_DataClientID]', N'FK_DataRequests_DataClients_DataClientID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_UserCountries_UserCountryID]', N'FK_DataRequests_UserCountries_UserCountryID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Websites_WebsiteID]', N'FK_DataRequests_Websites_WebsiteID_archive', N'OBJECT'

它为 Logging.tbDataRequests 及其键和索引编写重命名语句，但不为任何其他对象编写重命名语句。

我在这里做错了什么？

sql-server sql-server-2008

1 个回答

  1. Best Answer

    您不能以编码的方式遍历对象。这是工作代码：

    Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'xyzabc123'
$DatabaseName = 'test1'
$TableName = 'main'
$TableSchemaName = 'dbo'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

$Database = $Server.Databases[$DatabaseName]

$Table = $Database.Tables[$TableName, $TableSchemaName]

$TablesToArchive = @($Table)
$Database.Tables | Select -ExpandProperty ForeignKeys | % {
    if($_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName)
        { $TablesToArchive += $_.Parent }
}
# $TablesToArchive | % { $_.Name }

$ObjectsToArchive = ($TablesToArchive | Select -ExpandProperty Indexes) + ($TablesToArchive | Select -ExpandProperty ForeignKeys)
# $ObjectsToArchive | % { $_.Name }

$ObjectsToArchive | % {
    $_.Rename($_.Name + '_archive')
}
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text
# $RenameCommands

    例如，你期待

    $Database.Tables.ForeignKeys

    给你一个由每个对象ForeignKeys组成的组合集合。如果您添加了调试命令ForeignKeysTables

    # $ObjectsToArchive | % { $_.Name }

    你本来可以很快找到它的。

    • 6

相关问题