Продажи: 1-800-867-1389

 none
Trying to Delete Data Sync Tables

    Вопрос

  • I have been testing the Data Sync tool on a tiny table (4 rows) and the whole process worked perfectly - very quick and easy.

    Then I got ambitious and attempted to sync a table with 3  thousand rows, and 10 columns - nothing bigger than varchar(50).

    Unfortunately, the initial sync stage semed to hang and after about 8 hours, I decided to try and stop the process. I couldnt delete the Sync Group, so I stopped the Agent Service on the On Premises PC. This allowed me to delete the group.

    Then I tried to delete the tables that were created as part of the Sync Process - the tracking tables that Azure auto creates. I tried to drop them in Management Studio, using the GUI, but the program just hung for ages and the action was never completed.

    I had a look at the Views, to check for table locks or open transactions, but there doesn't seem to be a way to kill processes in Azure SQL?

    .....and now i am completedly locked out of the database management portal in Azure. I get a message that I may have too many connections from my IP  Address.

    So, with that scenario, I have two quesations:

    1. Is it possible for me to 'unlock' tables and kill transactions in Azure (if that is what the problem is), so that I can get back into the database management portal?

    2. Should I have been manually deleting those tracking tables, or will I always be 'locked out' from doing that?

    Thanks for your help

    Mike

     

    4 марта 2012 г. 13:38

Ответы

  • Hi there, Mike!

    We have some problems trying to delete some Data Sync groups. Finally I tell you what we did. Firs we check that Data Sync local agent was running ok in the local machine where SQL Server was. This is the important thing. To chek that is ok, you should be able to connect with this agent to your local server and their databases if it's ok that's the first step.

    Second, using this agent, unsuscribe SQL Server local databases with the correspondent button, connect and select database in the agent and click Unregister. You should see the message operation succeded or something like that and then you will see no more the database in the agent.

    Once unsusbcribed, it's Azure Data Sync configuration time. Go to your Data Sync in Azure and to the databases node under Sync Groups where appear your participating databases and delete the agent linked to your data sync group (firs delete the database reference in this node).

    When succeded i'ts time to delete the data sync group, if failed, there is an option to force delete in the panel with the graphical representation of databases and connections, in the main graphic area.

    That process allowed us to clear all data sync synchronization information.

    I hope this could help you. If so, please mark the answer as Answer or as Helpful. Otherwise please if you provide more info I'll be glad in trying to help

    Regards,


    Maick · augurarte :: Albacete · Spain : Guia practica Expression Web, Anaya Multimedia

    • Помечено в качестве ответа MikeBarry22 6 марта 2012 г. 22:45
    6 марта 2012 г. 6:59

Все ответы

  • OK, think I may have answered my own question. But in case anyone else cancels a Sync job, here is what seems to have happened.

    Since I couldnt find a way to cancel the Sync task in the Azure Portal, I forced it to cancel, by closing the local Agent, then deleting the Sync Group.

    This seems to have left thousands of unfinshed transactions/or tasks (not that familiar with them) which I found by using the following views

    sys.dm_exec_sessions

    sys.dm_tran_session_transactions

    sys.dm_tran_active_transactions

    sys.dm_exec_connections

    sys.dm_exec_sql_text

    sys.dm_tran_locks

    I couldn't find a way to clear these up and had to wait until Azure reset them. Once that happened, Azure let me back into the Portal.

    Perhaps if I had waited longer, the Sync job would have completed properly, but I didnt think it would take that many hours for a few thousand rows of data.

    4 марта 2012 г. 14:47
  • just a side note, please refrain from deleting those objects manually. if you really have to, aside from the tables, you will have to remove the triggers, stored procedures and a user-defined table types.
    5 марта 2012 г. 11:18
  • Hi there, Mike!

    We have some problems trying to delete some Data Sync groups. Finally I tell you what we did. Firs we check that Data Sync local agent was running ok in the local machine where SQL Server was. This is the important thing. To chek that is ok, you should be able to connect with this agent to your local server and their databases if it's ok that's the first step.

    Second, using this agent, unsuscribe SQL Server local databases with the correspondent button, connect and select database in the agent and click Unregister. You should see the message operation succeded or something like that and then you will see no more the database in the agent.

    Once unsusbcribed, it's Azure Data Sync configuration time. Go to your Data Sync in Azure and to the databases node under Sync Groups where appear your participating databases and delete the agent linked to your data sync group (firs delete the database reference in this node).

    When succeded i'ts time to delete the data sync group, if failed, there is an option to force delete in the panel with the graphical representation of databases and connections, in the main graphic area.

    That process allowed us to clear all data sync synchronization information.

    I hope this could help you. If so, please mark the answer as Answer or as Helpful. Otherwise please if you provide more info I'll be glad in trying to help

    Regards,


    Maick · augurarte :: Albacete · Spain : Guia practica Expression Web, Anaya Multimedia

    • Помечено в качестве ответа MikeBarry22 6 марта 2012 г. 22:45
    6 марта 2012 г. 6:59
  • Thanks Miguel, for the very detailed answer. I will follow that process if I run into that problem again. So far it happened only once. The other times it worked fine, and very fast too.
    6 марта 2012 г. 22:52