SQL Server 2000 change tracking doesn't stop

I enabled(checked by mistake) change tracking for a table with 8 million records.
After that SQL server has performed poorly and I haven't been able to
disable change tracking. If I try to disable change tracking by unchecking
it from the right mousemenu (in enterprise manages), enterprise manager freezes and nothing happents. I also tried to stop change tracking from query analyzer but the command was executing a long time and nothing happened.
kkilpiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>>enterprise manager freezes and nothing happents
Note that such commands can take some time, ie several hours!!!
I saw a similar thing yesterday where an action on a large table took some 5 hours!
CHeers
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Check out the SQL Server log file (LOG\errorlog) at the end, you might see if the action is still ongoing or not...
0
vjammyCommented:
Try running this command from the query analyzer

EXEC sp_fulltext_table 'Categories', 'drop'

This shall give you more informtation on sp_fulltext_table:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_fa-fz_9mcl.asp

The problem is that the you are probably using Background option with change tracking, and that is taking up most of your CPU and memory.

SQL Server 2000 introduces a new way to maintain the full-text indexes called Change Tracking population. Change Tracking population maintains a log of all changes to the full-text indexed data, and propagates the changes to the full-text index. There are three Change Tracking options:

Background
On demand
Scheduled
With the Background option, changes to rows in the table are propagated to the full-text index as they occur. You can use this option only when you have enough CPU and memory, as it can take an extremely long time.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vjammyCommented:
I mean run:
EXEC sp_fulltext_table 'Table_Name', 'drop'
0
kkilpiAuthor Commented:
I didn't manage to disable the change tracking so I deleted the whole table (I'm operating on a testing server).

I decided to change the data base structure and would need assistace to write a sql command.

CASE:

Table1 has fields: cID, cName, cClient, Ccustoms
and
Table2 has fields: cID, cName, cClient, cActionName

The table2 is used to store transaction records and table1 is used to store unique records of clients.

I would like to copy all cID & cName pairs and cClient from Table2 to Table 1 with-out duplicates. (Multiple rows in the table may contain the same cID with different cName values, so cID & CName should be unique as a pair in the Table2, none of the fields have to be unique alone).

If table1 has existing rows the command should remove the cID / Cname pairs from Table1 that doesn't have matches in table2 if the Ccustoms field of table 1 doesn't have a value. The command should also add new pairs from table2 to table1 and update the cClient field.

If possible the command would be used as tigger so the command doesn't need to be manually executed every time when table2 is modified.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.