In a change tracking enabled database I can find the latest change tracking version number by using
Select CHANGE_TRACKING_CURRENT_VERSION() As Latest ChangeTrackingID.
Which will give latest change tracking id (example 1022), Is there a way to find the datetime of this latest change tracking id.
We are developing an application that requires change tracking.We tested it in development and test environments and we are preparing our production deployment.The very first thing that needs to be done is an
ALTER
DATABASE [db_name] SET
CHANGE_TRACKING =
ON (CHANGE_RETENTION
= 2 DAYS,
AUTO_CLEANUP =
ON)
We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?
Hello,
we need to track date/time of last update for each record in a table.
As we understand it, we can't use field type Timestamp as this type does
not use dates/times.
Is there any SQL function available which we can bind to a column or
do we really have to use triggers?
Greetings from Mannheim, Germany
Ricardo
Hello,
we need to track date/time of last update for each record in a table.
As we understand it, we can't use field type Timestamp as this type does
not use dates/times.
Is there any SQL function available which we can bind to a column or
do we really have to use triggers?
Greetings from Mannheim, Germany
Ricardo
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies View RelatedIs it possible to change the default detection interval time to reduce to less than 5 seconds.
We have latency in trouble shooting the deadlocks and causing blockings more on our critical Production server.
so i have an update query that is updating 50k rows per batch. my question is how do i campture how long it is taking to update 50k rows per batch.
View 3 Replies View RelatedTrying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View RelatedI am using change tracking for tracking changes to an OLTP database for daily data warehouse load. I am tracking changes on 4 tables:
Site, Well, GasEngine, GasField,
I am using an SSIS package for our DW load and on completion, I store the results of the queries above into my own ChangeTracking table. I then use this as the last synced version for each table which I then use for the next DW load. I have the following change tracking settings:
CHANGE_TRACKING = ON
(
CHANGE_RETENTION = 10 DAYS,
AUTO_CLEANUP = ON
)
Now, the Site and Well tables may not have any changes for a few weeks or months but the GasEngine table will have changes every half hour and the GasField table will have changes every week. I have recently received a NULL value for Site changes query (below) and the SSIS package fails.
Is this due to there being no changes within the 10 days retention period?? What can I do in order to maintain the changes?? Could I maybe update the scripts above to check if the response from the query is NULL and if yes, set the current synced version to the last synced version from my ChangeTracking table?
Also, when the 10 days retention period is up, will the change tracking change version counter continue on from the last change version?
Has anyone come across a change tracking tool for SQL Server. Specificallythe scenario I want is the following :A Production DB needs some modifications to its content.This tool will copy the DB to a dev environment.The Dev environment will be 2 copies of the DB, 1 as a control set and theother the change DB.The developer makes their changes in the Change DB. They test them out andthen when they decide the changes are ready to pushto production they hitthe "go" button on the tool.The tool calculates the delta between the Control and the Change DB and thenpushes the changes to producitonAny ideasThankss
View 2 Replies View RelatedThis one is giving me quite a bit more difficulty then I ever imagined it would...
Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.
I can't seem to find a good way to do this.
How to reset the value of the change tracking anchor id?
CHANGE_TRACKING_CURRENT_VERSION()
It's a bigint that increments on every DML operation and I just wondered how you could reset it back to zero. Turning change tracking off and on doesn't seem to do it.
Is there a table where last DDL change date exists? For instance, if I have added a column to a table is there anywhere I can find when this occurred? Auditors are askng about this.
View 7 Replies View RelatedDoes anyone know of software that tracks changes to a database? Forexample, it would track anytime an SP or view was recompiled, or ifyou added or deleted a column to a database?
View 1 Replies View Relatedbest way to track changes for a very pesky table.We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.
So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.
For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.
The table has tons of fields in it but I only care about the 2 date fields for history purposes.Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):
CREATE TABLE [dbo].[Fake_Name](
[lin] [char](6) NOT NULL,
[boip_no] [char](6) NOT NULL,
[dt_tc] [varchar](25) NULL,
[Code] .....
method to track changes to this table with it being truncated every day?
How can i enable my fulltex change-tracking and update-index in my table?
I recreated my fulltext catalog and start the full population, but although my fulltext index status shows active, my full-text change-tracking and the update index were disabled. - and I don't know how to enable them.
Thanks in advance
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.
SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table
My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.
This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:
1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.
I realize this task is difficult, but am I at least starting in the right direction?
Experts Only Please. (jk)
Account getting wrongly updated due to concurrencyWe are using Sql 2014 and storing data in customer_account table for customer account details however we are experiencing wrong value insertion during concurrency ..pls find the code
Declare @date datetime2(7)
Declare @InsDate datetime2(7)
SELECT TOP 1 @Amountremaining =Remaining ,@Date=Datetime
FROM <customertaccounttable>
[code]....
How can i find the users of the table who updated it last (or) Owner of the table not the schema
View 4 Replies View Relatedhi,
i am currently in the process of moving a bunch of jobs into SSIS from another ETL tool. I would like to benchmark the two products against each other by comparing how long each step of an ETL process would take.
I see no way to do this in SSIS, there is the Progress tab but it doesnt list start/time and end/time. Plus I having loops and things which I want to know how long each iteration takes.
Is there a way to track all this?
Thanks
We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
Hi all
How to find out when the stastitics updated.
help me
Hi,
I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ??
I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies View RelatedHi,
Does anyone know how to find out how many rows have been updated or deleted in a particular table for the last 1 hour?
Please reply.
Best Regards,
Ansaar
I am trying to find a global way of when the last time a row in one of my tables was updated or data inserted. I say global because I don't want to drill down through each table looking for modified rows.
I am a DBA of several hundred databases and want to retire those no longer being used.
Is there a column of a system table that has this info.
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies View RelatedHi, I wonder where I can see last update over a table. Maybe sqlserver write some information about every table.
For example there are 300 tables in a database an I want to execute some query to see the last tables updated (list of tables updated > 2008-01-02 )
Hope to be clear,
Best regards
Ariel
I am reading about the RESTORE command to a point in time using logs, I would like to know the minimum point in time recovery for a backup image using T-SQL command before applying a log restore and what are the log ranges needed for the restore during restore.
My Version 2008 R2
I need to write down a sql query wherein in one particular day(user will enter manually), i need to find out a 15 minutes slot wherein purchase order's created or updated are the highest.
i.e. out of 96 slots(15 minute slot each)-I need to find the slot which has maximum number of Purchase orders created or updated.
SQL Server 2012R2 Express, I need to change db owner from Domain1User1 to Domain2User1.
Both SQL login Domain2User1 and db user Domain2User1 exist; db user Domain2User1 is mapped to SQL login
Domain2User1.
When I try to change db owner from Domain1User1 to Domain2User1
I get the error:
This article does not imply any restrictions on users that can be made db owners: [URL]....
"In SQL Server, the owner of the current database can be changed. Any user, a SQL Server login or Microsoft Windows user, who has access to connect to SQL Server can become the owner of a database."
Why can't I make the owner of a db an SQL login that have a mapped user in that db?
I have a problem upgrading a McAfee ePolicy Orchestrator which uses SQL Server 2008 R2 SP2. McAfee logs indicate that server names from SQL server and from McAfee installer does not match.It seems that the reason is that machine name was changed after SQL Server was installed.
MSDN instructions for sql server name changing give next commands to solve this problem:
SELECT @@SERVERNAME AS 'Server Name';
sp_dropserver <old_nameinstancename>;
GO
sp_addserver <new_nameinstancename>, local;
GO
But it didn't work for me, because "SELECT @@SERVERNAME AS 'Server Name'" shows correct (actual) name of sql server. So sp_dropserver says that there is now such instance.In the same time if I use this commands:
select serverproperty('MachineName')
select serverproperty('ServerName')
I get old (not actual) name.
epo-server is an actual computer name WIN-VUN5TSKAHG1 is an old name.