Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server 2000 change tracking doesn't stop

Posted on 2003-03-26
5
Medium Priority
?
424 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
  • 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

580 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