none
Upgrade TFS2010 -> TFS2015 prc_MigrateFile

    Question

  • Hey all,

    Currently we are doing a test migration of our TFS2010SP1 to a brand new TFS2015 box.

    Steps i've done so far:

                - Detach collection on TFS2010
                - Backup
                - Attached the collection on TFS2010 so ppl could continue work...
                - Restored on new TFS2015 box
                - Attached on TFS2015 box -> upgrade of collection went ok..

    The transferred collection is about 20Gb in size.

    only now.... The new server has been running for about 8 days, still performant and everything, builds are converted to vNext...

    we are almost ready to redo the above steps and run production on our TFS2015 box.

    one thing i've noticed, the TFS2015 server uses 100% CPU for SQL for 8 days now. Profiles shows me its doing prc_MigrateFile and prc_GetNextMigrateFileId all the time. The parameters to these stored procedures are always the same, so it seams like its in an endless loop. It appears the TfsJobAgent.exe is responsible for the executions of these storedprocedures.

    Can someone please advice me how to solve or where i should be start looking?

    Old Server specs:

    16 GB Memory
    TFS2010SP1
    Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64)   Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    New Server specs:
    16GB Memory
    TFS2015 RTMMicrosoft SQL Server 2014 - 12.0.4100.1 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    Thursday, August 27, 2015 1:52 PM

Answers

  • Marcel,

    I am sorry you ran into issue upgrading to TFS 2015. We found the root cause of the issue. You can execute the following statement in the configuration and all collection databases to fix the problem:

    ALTER PROCEDURE prc_GetNextMigrateFileId
        @partitionId    INT
    AS
    BEGIN
        SET NOCOUNT ON
        SET XACT_ABORT ON
    
        DECLARE @fileId INT
        DECLARE @dataspaceId INT
    
        -- Guard against null values by setting to 0 by default.
        SET @fileId = 0
        SET @dataspaceId = 0
    
        -- Get the next file Id that requires migration
        SELECT  TOP(1)
                @fileId = FileId,
                @dataspaceId = DataspaceId
        FROM    tbl_FileReference
        WHERE   PartitionId = 1
                AND MigrateFileId IS NOT NULL
    
        -- we drop tbl_AttachmentContent when we are done to cleanup any ghost records.
        IF (@fileId = 0)
        BEGIN
            IF EXISTS (
                SELECT  *
                FROM    sys.tables
                WHERE   name = 'tbl_AttachmentContent'
            )
            BEGIN
                --EEPR WHITELISTED:EXISTS check for tbl_AttachmentContent
                IF NOT EXISTS (
                    SELECT  *
                    FROM    tbl_AttachmentContent
                )
                BEGIN
                    DROP TABLE tbl_AttachmentContent
                END
            END
    
            IF EXISTS (
                SELECT  *
                FROM    sys.indexes
                WHERE   name = 'IX_tbl_FileReference_Upgrade_MigrateFileId'
                        AND object_id = OBJECT_ID('tbl_FileReference')
            )
            BEGIN
                DROP INDEX IX_tbl_FileReference_Upgrade_MigrateFileId ON tbl_FileReference
            END
        END
      
        SELECT @fileId AS 'FileId', @dataspaceId AS 'DataspaceId' WHERE @fileId > 0
    END

    You can execute this statement any time after upgrade.

    Sorry for the inconvenience that this have caused.

    Thanks,

    --Vladimir



    Tuesday, September 01, 2015 9:30 PM
    Owner

All replies

  • Hi,

    Are there any error messages in the event viewer?

    What’s the result if you restart your TFS server?

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 28, 2015 6:38 AM
    Moderator
  • Hi Starain,

    Sorry for the delay...Weekend was long and nice.

    No errors in the eventlogs, after a restart of the TFS server, the load is down for the first x minutes, then the same symptoms start all over. An endless loop of the named storedprocedures with a extreme load on SQL.

    Kind regards,

    Marcel

    Monday, August 31, 2015 6:42 AM
  • Hi,

    Are SQL Server database (DT) and TFS (AT) are installed on the same server? If so, what's the result if you use differernt SQL Server database.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, September 01, 2015 5:01 AM
    Moderator
  • Yes, DB and Application Tier are installed on the same machine.

    I'll install SQL Server to the build server too, so i can switch datatier to there to see if it brings a difference.

    Tuesday, September 01, 2015 6:45 AM
  • Database is now running on a second server.

    When i start SQL Profiler, the same symptoms happen on this server.

    Tuesday, September 01, 2015 9:25 AM
  • We have the same exact problem..
    Tuesday, September 01, 2015 9:29 AM
  • Seems to be a problem with the migration of the test attachments files.

    I was having the exactly same symptoms on a test migration.

    TFS2010sp1 => TFS2015RTM

    After more than a week with the data tier CPU at over 70%,  re-executed the migration, but first cleaning up all the test attachments of the collection and now the CPU on the data tier is idle most of the time.

    The only reference to a problem with prc_MigrateFile  i could find was on a post of Buck Hodges announcing that the developer division was running on TFS2012RC:

    http://blogs.msdn.com/b/buckh/archive/2012/06/08/developer-division-is-running-on-tfs-2012-rc.aspx

    In the post he mentions an issue about the migration of the test attachments related with prc_MigrateFile. Maybe is not the same issue but this give me a hint about where to look at.

    Regards

    Tuesday, September 01, 2015 12:44 PM
  • hmm.

    If i query the attachment table in TFS_DefaultCollection on the TFS2010 SqlServer, it says we dont have any attachments.

    Tuesday, September 01, 2015 1:06 PM
  • There are several Attachment tables on the collection database, I don't know exactly which holds the test attachments content. I cleaned (and queried) the attachments using the Test Attachment Cleaner tool provided by the TFS Power Tools.
    Tuesday, September 01, 2015 5:30 PM
  • To be honest, we've never used any test together with tfs.

    I did however run the cleaning tool, found nothing, but I'm kinda unsure if I used the right settings. Do you mind sending me your settings.xml you used?

    kind regards, marcel
    Tuesday, September 01, 2015 5:36 PM
  • This is my settings.xml:

    <DeletionCriteria>
      <TestRun />
      <Attachment>
        <SizeInMB GreaterThan="0" />
      </Attachment>
      <LinkedBugs />
    </DeletionCriteria>

    Anyway, maybe this is an attachment migration issue (not specific to test attachments)

    Tuesday, September 01, 2015 5:44 PM
  • Marcel,

    I am sorry you ran into issue upgrading to TFS 2015. We found the root cause of the issue. You can execute the following statement in the configuration and all collection databases to fix the problem:

    ALTER PROCEDURE prc_GetNextMigrateFileId
        @partitionId    INT
    AS
    BEGIN
        SET NOCOUNT ON
        SET XACT_ABORT ON
    
        DECLARE @fileId INT
        DECLARE @dataspaceId INT
    
        -- Guard against null values by setting to 0 by default.
        SET @fileId = 0
        SET @dataspaceId = 0
    
        -- Get the next file Id that requires migration
        SELECT  TOP(1)
                @fileId = FileId,
                @dataspaceId = DataspaceId
        FROM    tbl_FileReference
        WHERE   PartitionId = 1
                AND MigrateFileId IS NOT NULL
    
        -- we drop tbl_AttachmentContent when we are done to cleanup any ghost records.
        IF (@fileId = 0)
        BEGIN
            IF EXISTS (
                SELECT  *
                FROM    sys.tables
                WHERE   name = 'tbl_AttachmentContent'
            )
            BEGIN
                --EEPR WHITELISTED:EXISTS check for tbl_AttachmentContent
                IF NOT EXISTS (
                    SELECT  *
                    FROM    tbl_AttachmentContent
                )
                BEGIN
                    DROP TABLE tbl_AttachmentContent
                END
            END
    
            IF EXISTS (
                SELECT  *
                FROM    sys.indexes
                WHERE   name = 'IX_tbl_FileReference_Upgrade_MigrateFileId'
                        AND object_id = OBJECT_ID('tbl_FileReference')
            )
            BEGIN
                DROP INDEX IX_tbl_FileReference_Upgrade_MigrateFileId ON tbl_FileReference
            END
        END
      
        SELECT @fileId AS 'FileId', @dataspaceId AS 'DataspaceId' WHERE @fileId > 0
    END

    You can execute this statement any time after upgrade.

    Sorry for the inconvenience that this have caused.

    Thanks,

    --Vladimir



    Tuesday, September 01, 2015 9:30 PM
    Owner
  • Vladimir,

    This was the answer!

    Thank you and all the others for thinking with me and the help!

    Kind regards,
    Marcel

    Wednesday, September 02, 2015 6:07 AM
  • I hope this will be included in the next TFS 2015 service pack as I had the same issue as well. Huge time waster and inconvenience for the whole IT team.

    Thanks for the solution!

    Monday, September 21, 2015 3:45 PM
  • The fix will definitely be included in the TFS 2015 Update 1.

    Thanks,

    --Vladimir

    Tuesday, September 22, 2015 2:50 AM
    Owner
  • I'm having the same issue but unfortunately updating the stored proc as per above didn't help.  I note that in sql server profiler I'm getting these two calls repeated over and over again:

    exec prc_GetNextMigrateFileId @partitionId=1

    exec prc_MigrateFile @partitionId=1,@fileId=262291,@dataspaceId=2

    And if I just run the first stored proc directly as above I get the following back:

    FileId DataspaceId

    262291 6

    It seems like it's just ignoring the DataspaceId value that's returned and passing 2 for some reason?

    Wednesday, September 23, 2015 3:56 AM
  • Hi,

    could you execute the following statement in the collection database?

    SELECT *
    FROM    tbl_Dataspace
    WHERE   PartitionId = 1
            AND DataspaceIdentifier = '00000000-0000-0000-0000-000000000000'

    And this one?

    EXEC prc_GetServiceVersion 'FileContent'

    Thanks,

    --Vladimir

    Wednesday, September 23, 2015 5:41 AM
    Owner
  • Hi Vladimir,

    Output from the first query:

    Output from the second query:

    ServiceName Version MinVersion
    FileContent 3 3

    Thanks,

    Graeme


    • Edited by Graeme Baker Wednesday, September 23, 2015 8:48 AM
    Wednesday, September 23, 2015 8:45 AM
  • Hi,
    you can execute the following script in each collection database to workaround the issue. It might take awhile to run it depending on how many files must be migrated to the Team Foundation File Service:

    SET XACT_ABORT ON SET NOCOUNT ON DECLARE @t TABLE ( FileId INT NOT NULL, DataspaceId INT NOT NULL ) DECLARE @fileId INT DECLARE @dataspaceId INT WHILE (1 = 1) BEGIN DELETE @t INSERT INTO @t EXEC prc_GetNextMigrateFileId @partitionId = 1

    IF @@ROWCOUNT = 0 BEGIN BREAK END

    SELECT @fileId = FileId, @dataspaceId = DataspaceId FROM @t

    EXEC prc_MigrateFile 1, @fileId, @dataspaceId RAISERROR('Migrated file. FileId: %d, DataspaceId: %d', 10, 0, @fileId, @dataspaceId) END

    Again, sorry for the inconvenience that this have caused. The issue will be fixed in TFS 2015 Update 1.

    --Vladimir





    Wednesday, September 23, 2015 7:03 PM
    Owner
  • Hi Vladimir,

    That seems to have gotten it over that particular hump.  It's still busy processing away but it's not doing the same thing over and over again.  Thanks for your help!

    Again, sorry for the incontinence that this have caused. The issue will be fixed in TFS 2015 Update 1.

    Luckily that wasn't one of the symptoms I experienced ;)

    Oh! And worth mentioning the loop exit condition in the script is wrong, it just keeps on going spitting out "Migrated file. FileId: 0, DataspaceId: 0" after it processes the last file.

    One last thing for interested readers, it took almost 2.5 hours to run on my 9GB collection database.

    Seriously the last thing this time, I had to run the ALTER PROCEDURE script on my collection as well otherwise it continues spinning after doing the other processing.

    Cheers,

    Graeme




    • Edited by Graeme Baker Wednesday, September 23, 2015 11:36 PM
    Wednesday, September 23, 2015 11:25 PM
  • Hi Vladimir,

    We have the same problem with our TFS 2015 after migration from TFS 2010.

    is this an official fix ?

    is it supported by MS ?

    do we have to open an official case with MS to be fully supported ?

    thanks

    Thursday, October 15, 2015 3:42 PM
  • We have the same problem with our TFS 2015 after migration from TFS 2010.

    is this an official fix ?

    is it supported by MS ?

    do we have to open an official case with MS to be fully supported ?

    thanks

    Vladimir is posting with an offical MSFT account and therefore it is supported - especially as it we'll be fixed in a future update.

    you don't have to open an offical case but you should create a complete full backup of your TFS installation - in case anything goes wrong.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 15, 2015 6:48 PM
  • No offense, but you're not a Microsoft employee :-)

    I will wait for a Microsoft answer.

    thanks for your help

    Thursday, October 15, 2015 6:54 PM
  • hi Niko

    the fix is in TFS 2015 Update 1 RC.

    why aren't you installing Update 1 RC with go-live license if you suffer from this issue ?


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 15, 2015 8:07 PM
  • We are extremely cautious and we have strict rules to follow, and we went live just 2 days ago.

    I've decided to open a call with MS and let them decide if this fixe can be apply in our Production env.

    I will post their answer here.

    thanks

    Thursday, October 15, 2015 8:14 PM
  • Official fix will be available in TFS 2015 Update 1. This fix is definitely supported by Microsoft and applying it will not void support agreement. It is totally fine to open support case for this issue - I already got in touch with support team - they are aware of this issue and know how to fix it.
    I would like to apologize for the inconvenience that this issue have caused to customers upgrading from TFS 2010.

    Regards,
    --Vladimir

    Wednesday, November 04, 2015 1:05 AM
    Owner
  • Hi, thanks Vladimir.

    Just to confirm that applying the updated stored procedure and running the data update script has fixed the problem for us too.

    Thanks

    Chris

    Wednesday, November 04, 2015 11:50 AM
  • Thanks Chris!
    The fix for this issue is also in TFS 2015 Update 1 RC2, which can be downloaded from the following page:
    https://www.microsoft.com/en-us/download/details.aspx?id=49536
    Regards,
    --Vladimir


    Monday, November 09, 2015 3:52 AM
    Owner
  • Hi,

    we have been challenging the same issue when upgrading from TFS 2010 to TFS 2015.

    Unfortunately the proposed Vladimirs solution about altering the procedure prc_GetNextMigrateFileId was not working. The problem was that the only difference between current (TFS 2015) and Vladimirs version of this procedure is fact, that Vladimirs version ignores the input parameter @partitionId (in the select from tbl_FileReference table is direct condition for PartitionId = 1). But the input parameter from TFS server batch was always 1, so this change of procedure behavior has no impact on the provided result.

    The problem was in the second call of prc_MigrateFile procedure, where TFS server provides incorrect value for parameter @dataspaceId (as Graeme Baker pointed out).

    So we altered the database procedure and run Vladimirs script. After that all problems disappeared.

    BR

    Radim


    • Edited by Radim Bernatík Friday, November 20, 2015 12:07 PM I forgot to mention alter of the database procedure.
    Friday, November 20, 2015 8:20 AM
  • Radim,

    I changed 2 things in the prc_GetNextMigrateFileId:

    I replaced

    SELECT @fileId AS 'FileId', @dataspaceId AS 'DataspaceId'

    with

    SELECT @fileId AS 'FileId', @dataspaceId AS 'DataspaceId' WHERE @fileId > 0

    and replaced @partitionId with 1.

    You are absolutely correct that

        SELECT  TOP(1)
                @fileId = FileId,
                @dataspaceId = DataspaceId
        FROM    tbl_FileReference
        WHERE   PartitionId = 1
                AND MigrateFileId IS NOT NULL

    and

        SELECT  TOP(1)
                @fileId = FileId,
                @dataspaceId = DataspaceId
        FROM    tbl_FileReference
        WHERE   PartitionId = @partitionId
                AND MigrateFileId IS NOT NULL

    are functionally equivalent, but the first statement uses IX_tbl_FileReference_Upgrade_MigrateFileId index and the second one does not. First statement is super efficient, second one is effectively table scan, which causes high IO and CPU utilization:

    Here is a complete fix for the issue:

    ALTER PROCEDURE prc_GetNextMigrateFileId
        @partitionId    INT
    AS
    BEGIN
        SET NOCOUNT ON
        SET XACT_ABORT ON
    
        DECLARE @fileId INT
        DECLARE @dataspaceId INT
    
        -- Guard against null values by setting to 0 by default.
        SET @fileId = 0
        SET @dataspaceId = 0
    
        -- Get the next file Id that requires migration
        SELECT  TOP(1)
                @fileId = FileId,
                @dataspaceId = DataspaceId
        FROM    tbl_FileReference
        WHERE   PartitionId = 1
                AND MigrateFileId IS NOT NULL
    
        -- we drop tbl_AttachmentContent when we are done to cleanup any ghost records.
        IF (@fileId = 0)
        BEGIN
            IF EXISTS (
                SELECT  *
                FROM    sys.tables
                WHERE   name = 'tbl_AttachmentContent'
            )
            BEGIN
                --EEPR WHITELISTED:EXISTS check for tbl_AttachmentContent
                IF NOT EXISTS (
                    SELECT  *
                    FROM    tbl_AttachmentContent
                )
                BEGIN
                    DROP TABLE tbl_AttachmentContent
                END
            END
    
            IF EXISTS (
                SELECT  *
                FROM    sys.indexes
                WHERE   name = 'IX_tbl_FileReference_Upgrade_MigrateFileId'
                        AND object_id = OBJECT_ID('tbl_FileReference')
            )
            BEGIN
                DROP INDEX IX_tbl_FileReference_Upgrade_MigrateFileId ON tbl_FileReference
            END
        END
      
        SELECT @fileId AS 'FileId', @dataspaceId AS 'DataspaceId' WHERE @fileId > 0
    END
    GO
    
    SET XACT_ABORT ON
    SET NOCOUNT ON
    
    DECLARE @t TABLE (
        FileId      INT NOT NULL,
        DataspaceId INT NOT NULL
    )
    DECLARE @fileId INT
    DECLARE @dataspaceId INT
    
    WHILE (1 = 1)
    BEGIN
        DELETE  @t
       INSERT INTO @t EXEC prc_GetNextMigrateFileId @partitionId = 1
    
        IF @@ROWCOUNT = 0
        BEGIN
            BREAK
        END
    
        SELECT  @fileId = FileId,
                @dataspaceId = DataspaceId
        FROM    @t
    
        EXEC prc_MigrateFile 1, @fileId, @dataspaceId
        RAISERROR('Migrated file. FileId: %d, DataspaceId: %d', 10, 0, @fileId, @dataspaceId)
    END

    The issue is fixed in TFS 2015 Update 1.
    Thanks,

    --Vladimir


    Saturday, November 21, 2015 4:30 AM
    Owner