Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Archiving to avoid ID Exhaustion

We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in all referring tables so that we can continue to log new data into the tables.

The quickest way for us to archive data in one table is simply to rename the target table and all dependent objects -- junction tables, indexes, constraints. It's fast and keeps the data intact. To finish the job, we have to create new, empty copies of all the objects with their old names. If we do all these operations in one transaction, the stored procedures that insert new data will not fail because of missing tables.

An attempt to solve using PowerShell

I've putting together a PowerShell script to generate a T-SQL script to perform the archive operation.

The script is not generating as many rename statements as it should, and I don't understand why.

The script loads SMO, sets the names of target objects, and sets up a capture-only connection to the server - this is so I can capture rename commands for inspection later:

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

$DatabaseName = 'Logging'
$TableName = 'tbDataRequests'
$TableSchemaName = 'Logging'

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

Next it creates an array of objects that are going to be archived by renaming. The target table itself, all referencing tables, and all keys and all indexes of these tables are to be archived:

$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

The problem part is next. For each object to archive, I try to capture a rename statement.

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

Unexpected output

After running this against my database, the array $ObjectsToArchive contains 48 items. The expression $objectsToArchive.Name produces a list like this:

FK_DataRequestUUIDMappings_DataRequestUUIDs_Scrape RequestUUIDID

There should be one rename statement for every object in this array. However, the CapturedSql property contains only 10 rename statements. The expression $RenameCommands | ? { $_ -like '*sp_rename*' } produces a list like this:

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'

It's scripting out rename statements for Logging.tbDataRequests and its keys and indexes, but not for any of the other objects.

What am I doing wrong here?

share|improve this question
Would it be simpler to move the tables to a new schema, instead of renaming the objects? – Jon Seigel Dec 19 '12 at 20:09
Would it be simpler to increase the storage size of the field containing the keys? For instance, if you are using 32 bit integers, you could move to 64 bit integers. This would give you an exponentially higher number of IDs to use before running out. – Max Vernon Dec 19 '12 at 22:25
@JonSeigel Yes! My experiments show that the ALTER SCHEMA TRANSFER syntax automatically moves all keys, constraints, indexes, and so on to the same schema as the table on which they depend. Thanks for suggesting this. – Iain Elder Dec 20 '12 at 11:09
@MaxVernon Not this time, but I see your point. The archiving operating is easier and faster than the redesign work required to the types of the key column and all columns that refer to it. The logging database is supposed to hold a rolling few weeks' worth of data. The data is stored permanently in a data warehouse server. A lack of maintenance is why it now contains nearly two years worth of data. – Iain Elder Dec 20 '12 at 11:15
@isme: You're welcome. FYI, object names are unique within a schema. The only potential problem you could run into is doing metadata queries with object names without the associated schema name as well. I'm not sure what your processes are like, but watch out for that. – Jon Seigel Dec 20 '12 at 13:46
up vote 6 down vote accepted

You cannot loop through objects in the way you have coded. Here's the working code:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=, 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

For example, you were expecting


To give you a combined ForeignKeys collection made up of ForeignKeys of each Tables object. If you added the debug commands

# $ObjectsToArchive | % { $_.Name }

You would have been able to track it down very quickly.

share|improve this answer
Your script produces the same output as mine. For every execution of { $TablesToArchive += $_.Parent }, it also produces the error Method invocation failed because [Microsoft.SqlServer.Management.Smo.Table] doesn't contain a method named 'op_Addition'.. It's because the $( turns the one-element array $Table back into a Table object. You can't 'add' to a table. It's okay, I can fix this! – Iain Elder Dec 20 '12 at 10:49
I modified your script so that $Table is a Table and $TablesToArchive is initialized as a one-element array containing $Table. It works now! – Iain Elder Dec 20 '12 at 10:55
In my question I debugged using the expression $ObjectsToArchive.Name. PowerShell 3 supports Member Enumeration, which makes $ObjectsToArchive.Name equivalent to $ObjectsToArchive | % { $_.Name }. I should have been more explicit about my version. – Iain Elder Dec 20 '12 at 10:57
I've accepted this answer, but the edits I made that make it acceptable to me are pending peer review. They are the same as I described in earlier comments. – Iain Elder Dec 20 '12 at 14:24
I have accepted your edits since it's your question. FYI - it works for me on Powershell v1/2 as I have written (I haven't tested your change), so this comment should stick for visitors to know the difference. – 孔夫子 Dec 20 '12 at 20:44

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.