locked
Why is the dbo.__RefactorLog table created in the deployment script in my server project? RRS feed

  • Question

  • I'm curious to what changes to a server project could require a refactor log in the master database. I have only my logins defined in the project, and that's all that is deployed. Is it possible to disable the creation of the refactorlog table in the deployment script?
    Thursday, October 28, 2010 9:23 AM

All replies

  • You can have user created objects like stored procedures or tables which live inside the master database, which are getting deployed using a server project. Since you can refactor these kind of objects it will create the RefactorLog table. I was under the impression this was only the case though when a refectoring log file was being deployed with the project. Because the only usage is to validate if the refactoriung has been applied already. In a normal deployment this table does not get created as far as I can tell.
    GertD @ www.DBProj.com
    Thursday, October 28, 2010 7:26 PM
  • Thanks for your answer Gert. It makes more sense to me now.

    But if what you are saying is true about the RefactorLog table only being created when a refactoring log is deployed, then I'm still confused. I don't have a refactoring log in my server project, and I couldn't find any reference to anything that looks like refactoring in the project file (looking at the xml). The only thing I could find was in the deploymanifest file for my server project:

    <DeploymentExtension Include="Microsoft.Data.Schema.Sql.Refactoring.SqlRefactoringDeploymentContributor">
       <Assembly>Microsoft.Data.Schema.Sql</Assembly>
       <Version>10.0.0.0</Version>
       <Token>sD9ffxHVCjo=</Token>
      </DeploymentExtension>
    
    I can understand why the RefactorLog table is in my script when the above section is in my deploymanifest file, but why is the above section in my deploymanifest file?
    Friday, October 29, 2010 9:06 AM
  • I'd love to have a proper way to prevent the creation of this table during deploys, but for now I just added a section to my Script.PostDeployment.sql file.

    IF

     

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[__RefactorLog]') AND type in (N'U'))

    DROP TABLE [dbo].[__RefactorLog]

    GO

    Saturday, November 13, 2010 3:26 AM
  • Hello Sub-Star

    The refactoring log file is one of the configuration files which are passed to deployment extensions when you deploy. When you use refactoring to rename a database or to move a database object to another schema, the refactoring log file records that action. At deployment time, the information in the log file helps preserve the intent of your changes. For example, you might lose data if you renamed a table, because the table would be dropped and a table would be created with the new name. With the refactoring log file, the deployment script can instead rename the table, preserving your intent and your data.

    Thanks.


    Edwer Fang [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, November 17, 2010 4:24 AM
    Moderator
  • Thanks for your clarification Edwer. However, that doesn't answer my question (see my reply to Gert Drapers) about why the RefactorLog table is created when I don't have a refactorlog file in the server-project. Could you elaborate on that?
    Wednesday, November 17, 2010 10:04 AM
  • Hi,

    If there is a clean way to avoid it as asked, I’m interested too.

    Regards,

    Stef DBA

    Wednesday, December 8, 2010 2:58 PM
  • Ditto
    Friday, January 7, 2011 1:01 AM
  • The refactoring log file is one of the configuration files which are passed to deployment extensions when you deploy. When you use refactoring to rename a database or to move a database object to another schema, the refactoring log file records that action. At deployment time, the information in the log file helps preserve the intent of your changes. For example, you might lose data if you renamed a table, because the table would be dropped and a table would be created with the new name. With the refactoring log file, the deployment script can instead rename the table, preserving your intent and your data.


    Edwer Fang [MSFT]
    So how is it that the VS 2010 Database Projects never needed to deploy such a table to the target database? Isn't all the information needed stored in the project file? Why is the table needed?
    Friday, March 16, 2012 6:30 PM
  • VS2010 Database projects do deploy the __RefactorLog table.  The 'preservation of intent' behavior that this table supports was introduced in the GDR release, so at some point the table would not have been created.  It is marked as a system table so its not so easy to find.  The table and its usage is the same in SSDT.

    As to its purpose, it is used to identify which refactoring changes have been applied, and so shouldn't be applied again.  The ids are guids so that wherever the changes originate (could be from different projects) they can be correlated. 

    If you don't want the table created you should add script to delete the table in your post-deployment script (although this will not impact Schema Compare which doesn't currently process post-deployment scripts).

    Thursday, April 26, 2012 2:01 AM
  • VS2010 Database projects do deploy the __RefactorLog table.  The 'preservation of intent' behavior that this table supports was introduced in the GDR release, so at some point the table would not have been created.  It is marked as a system table so its not so easy to find.  The table and its usage is the same in SSDT.

    As to its purpose, it is used to identify which refactoring changes have been applied, and so shouldn't be applied again.  The ids are guids so that wherever the changes originate (could be from different projects) they can be correlated. 

    If you don't want the table created you should add script to delete the table in your post-deployment script (although this will not impact Schema Compare which doesn't currently process post-deployment scripts).

    My VS 2010 Database Projects never created such a table in scripts produced by schema comparisons. I have produced such scripts every week or two for the past couple of years, and examined each one in detail. Even when I added a new refactoring operation that appeared in the log file, no update to such a table appeared in the scripts produced by the schema comparisons. I never heard of this table until it started appearing in the scripts after I "upgraded" to SSDT. 

    I don't understand how this table could have been deployed or updated without my ever seeing it in a script. 

    - Mark

    Thursday, April 26, 2012 2:26 AM
  • The problem with your suggestion to add it to the post-deployment script is that if this table already exists the deployment script will fail before the post-deployment script can run.  In my case I have a referenced database and when the first database project is deployed there  is no problem.  I have a second database project that updates the first database and is deployed independently depending upon the installation requirements.  But since the second project it is deploying objects to the first database it wants to create the table which already exists.  Unfortunately the generated code does not check for the table's existence before it issues the CREATE Table statement  (very bad form) so the deployment script fails.  Since the statement is added after transactions have been committed I get all of my new objects but the script fails before the post-deployment script can run.  My only option seems to be to add it to the post-deployment script of the first project. 

    This really should be an optional "feature".  Where is the flag internally that tells VS to generate this statement?  Why can't I turn it off?

    Wednesday, November 21, 2012 9:19 PM
  • Hi Ron,

    SSDT generates the deployment script based on the then current state of the database - this is intrinsic to SSDT's declarative approach.  I think the problem lies in the fact that you are generating the second script before deploying the first, so there is no refactor log table present (because you dropped it after the previous deployment).  If you had deployed the first script then generated the script, the generation process would have seen the refactor log table was present and would not have generated a create statement again.  Does your deployment process allow you change the order in which you take these actions?  

    Cheers, Bill

    Wednesday, November 21, 2012 11:21 PM
  • Just in case I will add a way to VS2012:

    • Close a database project
    • Open the file. sqlproj of a database project into text editor
    • Find and replace <RefactorLog Include ="<Some name>.refactorlog" /> on <None Include="<Some name>.refactorlog" />
    • Open a database project and generate publish script
    • Table __RefactorLog should not be in the script

    Perhaps this method is also suitable for VS2010.

    Wednesday, August 28, 2013 9:21 PM