Hot answers tagged

8

You are looking at the wrong place. You have to check as below : Using T-SQL .. Use database_name go ALTER FULLTEXT INDEX ON schema.table_name SET CHANGE_TRACKING AUTO; Once done, you can check the status of the last populated datetime -- script source : http://stackoverflow.com/a/10505496/1387418 -- Modified by Kin on Dec 14' 2015 to reflect the ...


6

No, there isn't any. Any sort of 'last updated at' tracking would run into a severe performance problem as all updates, from all transactions, would attempt to update the one record tracking the 'last updated at'. This would effectively mean only one transaction can update the table at any moment, and all other transactions have to wait for the first one to ...


4

If the source is insert-only give it an IDENTITY column. When you do your data transfer you log the highest value written across. During the next transfer you need only query for values greater than that logged during the previous transfer. We do this for transferring log records to a data warehouse. For updatable rows add a "dirty" flag. It will have ...


4

Some of the implementation specifics depend on exactly what type of behaviour you need from this table (e.g. FIFO), but a typical design for a queue table uses DELETE with an OUTPUT clause: DELETE TOP (n) FROM QueueTable WITH (ROWLOCK, READPAST) OUTPUT deleted.RecordID; Or if ordered removal is required: DELETE FROM ( SELECT TOP (n) RecordID FROM ...


3

One doesn’t use the min_valid_version to track the changes. This is only used to validate if your client has to be reinitialized, if the metadata has been cleaned up before client could consume the changes. CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL) Obtains the minimum version that is valid for use in obtaining change tracking information from the ...


3

It looks like I'm two years late to the game, here, but there is indeed a pretty lightweight way of doing what you're asking for. There are two SQL Server mechanisms that can help you. Your ultimate solution might be a hybrid of the two. Data change detection. SQL Server has the capability of placing specific tables under watch, recording only which ...


2

I have worked out what is going on. The bad assumption I have made is that if a change causes 4 rows to be updated at once, that would result in the SYS_CHANGE_VERSION of the table going up by 4. It doesn't. Although there are four changes in the changetable, the version only goes up by 1 (it's a single change that changed multiple records.) Anyone ...


2

does anyone know if it's possible to use Change Tracking on a replication target? When you say replication target, do you mean subscriber ? Irrespective, of it, CT will work with replication. Things to consider when implementing Change Tracking (CT): Make sure you have retention & clean up of the data in the hidden CT tables. Enable CT on the ...


2

I have been able to find the documentation that I was looking for. CDC is supported off of the readable secondary but Change Tracking is not unfortunately. The link is specified below but here are the relevant parts for my needs. Redirecting the Query Load to a Readable Secondary While in many cases a client application will always want to connect to ...


2

It sounds like, for this particular scenario, you should run a trace in SQL Profiler. You can open it from the Tools menu in SSMS or find it in the Start > Applications > SQL Server 2008 R2 > Performance Tools folder. Create a new trace and connect it to your database. Use the "blank" template and in the Events Selection tab just select Stored Procedures > ...


2

http://technet.microsoft.com/en-us/library/hh710064.aspx talks about monitoring Change Tracking, however it neglects to mention anything about performance other than "Change Tracking is lightweight". It seems you're out of luck...


1

My understanding of Change Tracking is that it is per database transaction. This means that all changes that are part of the same transaction get the same SYS_CHANGE_VERSION. I also understood that the SYS_CHANGE_VERSION is set during the commit of the transaction, so numbers are a) always ascending and b) may be interleaved. You should always process the ...


1

As Aaron mentions, this logic could be done in a view rather than a function. If you do need to use a function, however, an alternative might be to use sys.change_tracking_tables rather than CHANGE_TRACKING_MIN_VALID_VERSION. It has a min_valid_version column, and as far as I can tell from the documentation it provides the same information you are looking ...


1

You should separate the tests into tests that can execute inside a transaction and rollback and tests that require a full restore. Execute accordingly :) If you have any DDL in the test then don't bother with transactions, the only safe option is to restore. Most single threaded DML tests will work if wrapped in a transaction and rolled back, but not all. ...


1

I reproduced this using the code below, and essentially, yes, if you wait long enough, and auto cleanup is on, it will clean itself out. It appears to be bound the a minimum - that is, the change_retention setting is more of a suggestion than a command. Admittedly, I haven't tested with real-world table activity, etc., but the links below contain more ...


1

Please look at the MySQL Documentation again A record of metadata operations such as those just described are written to the file ddl_log.log, in the MySQL data directory. This is a binary file; it is not intended to be human-readable, and you should not attempt to modify it in any way. ddl_log.log is not created until it is actually needed for ...


1

Windows Azure SQL Database (WASD) does partially support triggers so you could roll your own auditing. See here. Here's a really simple example: CREATE TABLE dbo.Users ( userId INT IDENTITY PRIMARY KEY, userName VARCHAR(30) NOT NULL UNIQUE, dateAdded DATETIME NOT NULL DEFAULT GETDATE(), addedBy VARCHAR(30) NOT NULL DEFAULT 'admin', ...


1

Don't be tempted to re-invent the wheel. This problem has been solved many times before you came across it. Take a look at schema comparison tools, such as those available in SQL Server Data Tools (SSDT), and commercial options like Red-Gate SQL Compare. Going forward you can also use DDL Triggers to log certain DDL events, helping you catch outlying ...


1

If the CDC/CT data is written to the local database it'll be available on the read only secondary.


1

This sounds like a situation where Oracle Change Data Capture would be appropriate. You would need to write some code that would subscribe to changes to this table and process them by pushing the changes to your SAP BW system. You can also use Oracle Streams to do heterogeneous replication from Oracle to a non-Oracle database using an Oracle Transparent ...


1

You can also use ApexSQL Data Diff. It has command line, so you can schedule the synchronization I've used both RG and ApexSQL. ApexSQL has only 1 version (not basic and Pro, like RG), so the fetures that are available only in RG SQL Data Compare Pro version available in ApexSQL 'basic' version. And ApexSQL is cheaper



Only top voted, non community-wiki answers of a minimum length are eligible