?
Solved

SQL Server 2000 change tracking doesn't stop

Posted on 2003-03-26
5
Medium Priority
?
412 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:kkilpi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8209340
>>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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 8209345
Check out the SQL Server log file (LOG\errorlog) at the end, you might see if the action is still ongoing or not...
0
 
LVL 1

Accepted Solution

by:
vjammy earned 2000 total points
ID: 8209486
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
 
LVL 1

Expert Comment

by:vjammy
ID: 8209488
I mean run:
EXEC sp_fulltext_table 'Table_Name', 'drop'
0
 

Author Comment

by:kkilpi
ID: 8209958
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

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question