SQL Server Jobs – How to Trim Table Output for a Specific Job

I answered this question in my blog at Sqltimes.wordpress.com As far as I know, the only way to configure retention period is by updating the retention column in the msdb.dbo.cdc_jobs table. You want to update the retention column of the record with 'cleanup' in job_type column. It could be done in two ways:

EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 349082; UPDATE msdb.dbo.cdc_jobs SET retention = '666000' WHERE job_type = 'cleanup'

If you have more than one CDC running, then using the appropriate CDC instance name.

A Little more info:

When the CDC cleanup job runs, it purges all data in the CDC tables up to this date. You can stop the CDC cleanup job any time while it is running. When you start it, it will pick up from where it left off. So if you stopped the job while it is running today and start to run it tomorrow it will start from where it left off yesterday and perform purge until the retention period mentioned in the msdn.dbo.cdc_jobs table.

CDC is pretty robust technology.

Hope it help, _UB

PowerShell offers the ability to do things in many different ways, so this is just the method I would do in solving the problem at hand, based on the information provided.

SMO offers the ability to check the table class to more efficiently find the database in question that contain the table and column specified; instead of trying to use INFORMATION schema views that some folks have a case against ever using them.

I would break this up into sections that will make it more easy to debug. I am assuming you are not worried what UNC path is tied to which server, just making sure the UNC path exist and the file has been moved. So I would first pull all the UNC paths from any database I find the table and column exist.

#Load assembly
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$serverList = 'server1','server2','server3\instance1','server3\instance2'

$searchTableName = 'File'
$searchColumnName = 'Location'

$uncPaths =
foreach ($s in $serverList)
{
    $srv = new-object microsoft.sqlserver.management.smo.server $s

    # The first "where" filters for the database with the specified table name
    # The second "where" filters for the column to exist within the table
    # The "-ExpandProperty Parent" is to go backwards in order to output just 
    #   the database name that contains the table and column
    $dbList = $srv.Databases | select -ExpandProperty Tables | 
        where {$_.name -match $searchTableName} | 
        select -ExpandProperty Columns | where {$_.name -match $searchColumnName} | 
        select -ExpandProperty Parent | select -ExpandProperty Parent |
        select -ExpandProperty name -unique

    # If $dbList count is greater than zero means it found a database
    if ($dbList.Count -gt 0)
    {
        #now we need to pull the location paths
        foreach ($d in $dbList)
        {
            $qry = "SELECT [Location] FROM $($searchTableName)"

            # The "-ExpandProperty" can also aid in outputting an object as just a string value
            Invoke-Sqlcmd -ServerInstance $s -Database $d -query $qry | select -ExpandProperty Location
        }
    }
}

# this variable should now contain all the UNC paths you need
$uncPaths

Now in regards to changing the path to the old location I would do something like this when I am iterating through the $uncPaths variable:

foreach ($f in $uncPaths)
{
    $oldPath = $f.Replace("Current","Old")
}