none
Error on upgrading from SQL Server 2005 to 2008 R2 from version 628 to version 629 RRS feed

  • Question

  • We having dificulties in restoring a SQL Server backup from 2005(x86) to 2008 R2(x64). We used a typical backup/restore combination to a file. After importing data (aprox. 90GB), it gets to 100%, and then an error appears:

    8631 - Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

    We think the problem is related to a problem in one of the upgrade steps:

    This is the output:
    10 percent processed.
    20 percent processed.
    30 percent processed.
    40 percent processed.
    50 percent processed.
    60 percent processed.
    70 percent processed.
    80 percent processed.
    90 percent processed.
    100 percent processed.
    Processed 11289048 pages for database 'My_Database', file 'FILE_Data' on file 1.
    Processed 188 pages for database 'My_Database', file 'FILE_Log' on file 1.

    Converting database 'My_Database' from version 611 to the current version 661.
    Database 'My_Database' running the upgrade step from version 611 to version 621.
    Database 'My_Database' running the upgrade step from version 621 to version 622.
    Database 'My_Database' running the upgrade step from version 622 to version 625.
    Database 'My_Database' running the upgrade step from version 625 to version 626.
    Database 'My_Database' running the upgrade step from version 626 to version 627.
    Database 'My_Database' running the upgrade step from version 627 to version 628.
    Database 'My_Database' running the upgrade step from version 628 to version 629.
    Msg 3167, Level 16, State 1, Line 1 RESTORE could not start database 'My_Database'.
    Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
    Msg 8631, Level 17, State 1, Procedure our_test3, Line 3 Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.


    So, we cannot get the database online. Because of this error, we have tried to run the Upgrade Advisor on the source server, but the upgrade advisor cannot run across step 6, and it fails.

    We have also tried to detach the database from the source server, copy the files to the new one, and attach the database, without luck.

    Is there any known workaround to this problem? In spite of the error on the procedure, this might not be the root cause of the problem, but we are not sure.

    Any feedback might be helpful.

    Thanks
    Thursday, February 10, 2011 8:52 PM

Answers

  •  

    We finally learnt that there is stored procedure in the database that uses DUMP statement which is not compativle with SQL Server 2008.Replacing DUMP with Backup takes care of the issue.

    Tuesday, April 12, 2011 4:04 PM

All replies

  • Hi Bruno, could I ask for a couple extra pieces of info? What edition is your sql 05, what version is your sql 08, and how are you restoring? i.e. SSMS, T-SQL, custom script or third party tool? Thanks.
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, February 10, 2011 9:25 PM
  • Hi Pete. The source server is plain sql 05 (x86) and destination server sql 08(x64) R2. I'm using SSMS to restore the database. Thanks

    Thursday, February 10, 2011 9:53 PM
  • Hi Bruno, sorry, I mean, are they both Enterprise edition, is one of them Standard edition, etc. Can I also ask if you ran DBCC CHECKDB before the attempted migration?
    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, February 10, 2011 10:02 PM
  • Ok, the source one is Standard Edition, and the destination is Enterprise. No, I didn't check before the migration. Can it make the backup useless? I tried to restore the backup on another SQL Server 2005 and it worked, so I'm assuming the backup is good.
    Thursday, February 10, 2011 10:13 PM
  • Well, 3013 is normally a write failure during the backup creation, but if you can restore the backup to a different box, then this is unlikely to be the cause, so I would certainly suggest running CHECKDB, to rule out a corruption if nothing else. It is always suggested best practise to do this before a migration anyway.

    Please post back, to let us know the outcome of this.

    Thanks.


    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Thursday, February 10, 2011 10:33 PM
  • Ok, I will try that and post back when I have news.

     

    The thing that I found I little bit strange was that the upgrade shoud run "from version 611 to the current version 661.", but it stops earlier on 'running the upgrade step from version 628 to version 629.' and then the error 3167, and so on.

    Anyway, I will post back.

    Thanks

    Thursday, February 10, 2011 11:44 PM
  • Well, it looks like I have a problem on the procedure (it is a view, indeed). I dumped it into a file and it's 12MB in only 8lines.

    I will try to work around on this problem, and then post back if I came on the solution.

    Thanks

    Friday, February 11, 2011 11:46 AM
  • Did you ever find the answer to the issue. I am running in to similar problem where restore of SQL Server 2000 backup on a 2008 system stops at step: upgrade step from version 628 to version 629 with an error number of 3013.
    Sunday, April 3, 2011 10:54 PM
  • Hi Bruno & Viv,

    I have seen at-least 3 cases of this issue before and it seems people are running into this more often now. The best way to resolve this identify the SP listed in the error message and try to simply that.

    Note that as part of the upgrade process, all the existing sql code has to be written into the new sql modules and dependency tables and it is failing at that moment.


    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    Monday, April 4, 2011 4:51 AM
    Moderator
  • Sankar,

     Thanks for the response. I was wondering if there is a way to find out which procedure or view is having the issue. When I restore the database along with the error 3013, the errors that gets logged in the SQL Server log are:

    Message
    Error: 928, Severity: 20, State: 1

    during upgrade, database raised exception 102, severity 25, state 1, address 013388DD. Use the exception number to determine the cause.

     

    I am not sure with this information along with error # 3013 how do I find out which procedure or view is causing the failure. If you have any idea please let me know. I would really appreciate any further insight in to the issue.

     

    Tuesday, April 5, 2011 6:35 PM
  • Hi Viv,

    >>Msg 3167, Level 16, State 1, Line 1 RESTORE could not start database 'My_Database'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Msg 8631, Level 17, State 1, Procedure our_test3, Line 3 Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

    Usually the procedure name is listed above along with the error message. See the bold font.


    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    Tuesday, April 5, 2011 6:48 PM
    Moderator
  • Sankar,

    Code I am using to restore is like:

    BEGIN

    TRY

     

     

     

    RESTORE DATABASE CLINK FROM disk = 'D:\ckinkbackup.bak' WITH REPLACE

     

     

     

     

     

    END TRY
     

     

    BEGIN CATCH
     

     

     

    SELECT Error_number()

     

    select ERROR_MESSAGE ()
     

     

    select ERROR_PROCEDURE ()
     

     

    select ERROR_LINE ()
     

     

    END CATCH

     


     

    Intertesting thing is if I restore the same database unsing management studio or without the try catch block it completes successfully.

     All I see in the SQL Server log or as part of try catch block error handling is what I listed in my previous post. There are no other error messages other than that. The restore stops at:

    Processed 840 pages for database 'CLINK', file 'clink_dat' on file 1.

    Processed 3 pages for database 'CLINK', file 'clink_log' on file 1.

    Converting database 'CLINK' from version 539 to the current version 655.

    Database 'CLINK' running the upgrade step from version 539 to version 551.

    Database 'CLINK' running the upgrade step from version 551 to version 552.

    Database 'CLINK' running the upgrade step from version 552 to version 611.

    Database 'CLINK' running the upgrade step from version 611 to version 621.

    Database 'CLINK' running the upgrade step from version 621 to version 622.

    Database 'CLINK' running the upgrade step from version 622 to version 625.

    Database 'CLINK' running the upgrade step from version 625 to version 626.

    Database 'CLINK' running the upgrade step from version 626 to version 627.

    Database 'CLINK' running the upgrade step from version 627 to version 628.

    Database 'CLINK' running the upgrade step from version 628 to version 629.

    ERROR messages in SQl Server error log are:

    Message
    Error: 928, Severity: 20, State: 1.


    Message
    During upgrade, database raised exception 102, severity 25, state 1, address 013388DD. Use the exception number to determine the cause.

     

    Error in the try catch block is:

    Error_number 3013

    ERROR_MESSAGE RESTORE DATABASE is terminating abnormally.

     

    Any idea about what might be going on and what might be triggering the failure when restore is done in the try catch block.

     

     

    Tuesday, April 5, 2011 10:06 PM
  • Viv,

    TRY CATCH block doesn't capture all of the errors and it's a limitation in the current framework. Msg 3013 is generic for database restore and not much to go on. Have you checked if you have any views in that database? Are they nested? If NOT then your problem may be different from Bruno's.

    Are both the server's (where the backup was taken & where its being restored) have same edition of SQL Server like Enterprise etc... Are you using any features that are enterprise only? Have you checked in the SQL Server error logs to see if there is any additional information.


    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    Tuesday, April 5, 2011 10:38 PM
    Moderator
  •  

    We finally learnt that there is stored procedure in the database that uses DUMP statement which is not compativle with SQL Server 2008.Replacing DUMP with Backup takes care of the issue.

    Tuesday, April 12, 2011 4:04 PM
  • Old post - just adding some value for future readers. The key to understanding the upgrade failure's cause is the too terse "exception 102, severity 25, state 1".

    Running:

    select severity, text 
    from sys.messages 
    where message_id = 102
    and
    language_id =serverproperty('LCID')
    For language_id = 1033, the above T-SQL returns:

    severity text
    -------- ------------------------------
          15 Incorrect syntax near '%.*ls'.

    "DUMP" syntax was discontinued in SQL Server 2008 (as Viv noted).

    SQL Server raises the severity of an error, depending upon circumstances at the time when the error was raised. An engine upgrade that experiences such errors should be fatal (hence Sev 25). Even so, I think Microsoft's SQL Server Developers should have been handled this situation far more gracefully. 




    Wednesday, April 23, 2014 2:56 PM