Take the 2-minute tour ×
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. It's 100% free, no registration required.

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=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

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:

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

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
2  
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
1  
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

1 Answer 1

up vote 5 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=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

For example, you were expecting

$Database.Tables.ForeignKeys

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

 
discard

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.