Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Kris Janssen 210 posts 569 karma points c-trib
    Jul 19, 2014 @ 00:20
    Kris Janssen
    0

    U7 Unable to delete old templates (again)

    Hi guys,

    I have some old templates in a site that I'm not using anymore and that I would like to get rid of.

    Deleting them results in YSOD:

    The DELETE statement conflicted with the REFERENCE constraint "FKcmsDocumentcmsTemplate_nodeId". The conflict occurred in database "YourSite", table "dbo.cmsDocument", column 'templateId'. The statement has been terminated.

    I checked the DB tables. None of the templates that I tried to get rid of are used in currently published pages.

    However, the template IDs are present in some of the "rollback" history entries.

    Unfortunately, there is no mechanism to purge this rollback history (why actually??) and if I try stuff like:

    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
    

    Then I can in fact delete the offending templates but thats not good either because then afterwards:

    exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
    

    Will fail because the constraints are probably still being violated.

    Can somebody please help me out here? And oh, most online SQL scripts to somehow try and delete rollback entries do not work.

  • Kris Janssen 210 posts 569 karma points c-trib
    Jul 19, 2014 @ 09:11
    Kris Janssen
    0

    I did more checking to illustrate the issue:

    Using FALM housekeeping package I can see that e.g. my node 1693 has a rollback version dated: 25/06/2014 15:13

    enter image description here

    Wen I issue following query on my DB:

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT TOP 1000 [nodeId]
      ,[published]
      ,[documentUser]
      ,[versionId]
      ,[text]
      ,[releaseDate]
      ,[expireDate]
      ,[updateDate]
      ,[templateId]
      ,[alias]
      ,[newest]
      FROM [Catsense].[dbo].[cmsDocument]
      WHERE [templateid] = 1692
    

    where 1692 is a template that is undeletable I get one result:

    enter image description here

    Which is, in fact, the rollback history item.

    I believe this to be an issue. Upon deletion, the FK constraints naturally need to check if a certain template ID is not being used, if this is not checked, results could be bad.

    However, either we are presented with a mechanism to prune the rollback history or the constraint check should only be run against the most recent content item (which is probably not possible).

    The other issue for me seems to be that, using FALM housekeeping, I cannot delete my rollback history due to "transaction log full" errors but with my hosting company I have very little control over the settings in that regard.

    Template deletion I have solved like so (for the moment):

    update dbo.cmsDocument
     set templateId = NULL
     where templateId = 1503
    

    Basically forcing the template to be NULL on the offending rollback items. However, I would still like to get rid of the current rollback items as well, there are just too many.

    Any thoughts and hints are still welcome.

  • John Hildebrant 14 posts 35 karma points
    Aug 05, 2014 @ 18:32
    John Hildebrant
    0

    I've got the same issue...

  • John Hildebrant 14 posts 35 karma points
    Aug 05, 2014 @ 18:34
    John Hildebrant
    0

    I've got the same issue 

  • Kris Janssen 210 posts 569 karma points c-trib
    Aug 05, 2014 @ 18:59
    Kris Janssen
    100

    Hi John,

    Do you mean that you have templates that you cannot delete?

    In that case you could try manually editing directly on the DB:

    update dbo.cmsDocument
    set templateId = NULL
    where templateId = 1503
    

    Like I mentioned earlier.

    Specifically for using FALM to get rid of rollback items, I can confirm that after kindly pushing my hosting provider to increase the allowed size of the transaction log (to unlimited in my case), FALM got rid of rollback items just fine.

    Therefore this issue is perfectly manageable, if you know where to look.

    Hope that helps.

  • John Hildebrant 14 posts 35 karma points
    Aug 06, 2014 @ 16:29
    John Hildebrant
    0

    Hi Kris,

    Thank  you for your reply. Yes, I have two templates that I can't delete. I don't have any documents currently using the first template ID in question. The second template's alias does not even exist in the cmsTemplate table.

    Thanks,

    John

Please Sign in or register to post replies

Write your reply to:

Draft