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.
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View RelatedHas 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.
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?
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?
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
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.
I'm looking for monitoring page file usage for SQL Server. Ultimately, I'm trying to prove that there is not a problem with a server and that it is not struggling with the workload, but someone has looked at page file usage and suggested there is a problem.
I've set up performance counters running from a separate server, but the counters relating to page file usage seem wildly inaccurate. E.g., I've got (_Total\% Usage), and (Total)\%Usage Peak), with Total Usage showing a constant value of 64, and the peak showing 92. I've also got Process (sqlservr)Page File Bytes and Page File Bytes Peak, which are showing values like 61,275,688,960 and hardly dropping below this.
If I look at Performance Monitor directly the values shown in here correlate with this, as shown in my first screenshot. However, if I look at resource monitor and look at disk activity, you can see that there is basically nothing going on (screenshot 2).
The server is more than equipped to deal with the workload, and looking at other counters for SQL there is no indication of problems, e.g. page life expectancy has a minimum value showing of 576,258, there is never less than 6GB of free RAM, SQL compilations are at a maximum of 5% of batch requests.
It is a physical server with 2x 8 core multi threaded CPUs, 64GB RAM with 58GB as the SQL server maximum. Hopefully this is enough info, but I'm happy to check anything else.
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 RelatedMsg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
I would like to get extract the first row of some time based data where any of the other values changes. For example, in the following 10 rows
ABDTE
112015-01-01
112015-01-02
112015-01-03
212015-01-04
112015-01-05
1NULL2015-01-06
1NULL2015-01-07
212015-01-08
212015-01-09
222015-01-10
I want to remove the rows where A and B are the same as on the previous row. So rows 2, 3, 7 and 9 should be eliminated. Note that A and B can have the same values multiple times, just not in succession in the extract. I've tried ranking but I can't figure out how to keep it from lumping all the values of A and B in the same group. The following incorrectly eliminates rows 5 and 8:
;with data as (
select 1 as A, 1 as B, '2015-01-01' as DTE union
select 1 as A, 1 as B, '2015-01-02' as DTE union
select 1 as A, 1 as B, '2015-01-03' as DTE union
select 2 as A, 1 as B, '2015-01-04' as DTE union
[Code] .....
Of course the real data has many columns and multiple data types that can have nulls. I just want get the row when anything changes. Is there a slick way to do this in SQL?
Our client is asking to display the 'parameter panel in right side of the report' insted of top.
how can I do this?
What issues will I encounter if I change the MSSQLSERVER password?
View 2 Replies View RelatedI create a db by following code. Now I want to change name of FILEGROUP. How can I do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
FILENAME = 'c:datamy1')
[Code] .....
I create a db by following code. now i want to change name of FILEGROUP. how can i do it?
CREATE DATABASE MyDb
ON
PRIMARY ( NAME = MyDat,
FILENAME = 'c:datamydat.mdf'),
FILEGROUP MyGroup CONTAINS FILESTREAM( NAME = My,
[Code] .....
how to change the first <TD> to add BGCOLOR in this script:
declare @body varchar(max)
-- Create the body
set @body = cast( (
select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )
[code]...
it runs over night, pulls 10.5m rows. Inserts into a table, from a select (so "insert...select", rather than "select into"), from many tables, grouping on a max. It's complex. During the day, it runs fine - maybe 25 minutes. At night it *sometimes* runs fine, but then sometimes takes 4hours.Checking this morning there were 230 threads open for this one query. Checking sys.dm_os_tasks and sys.dm_os_waiting_tasks there were no other wait types on that session_id. None at all. Checking activity monitor, most of the existing threads were suspended on the insert.
There are 24 cores, but NUMA'd. We have maxdop on the server of 8. The maxdop option on the query is 12, just to speed up the select. Index and Stats refreshed daily. We've eight identical tempdb data files, on a separate spindle. Checking those, they are filling up using the round robin correctly
Would the delay be due to SQL trying to combine so many 'select' threads into one 'insert' thread (as it can't insert in parallel; 2014 can, apparently. Upgrades not available!)Should i change the SP to run the select into a temp table (table variable?) with a maxdop of 12, then do the insert into the actual table using a maxdop of 1. Checking the execution plans for a table variable implies the subtree cost comes down from 2.5m to 357k. What's best - temp table or table variable?
How to change the sql service passwords on a SQL 2012 cluster.
View 3 Replies View RelatedAs the title suggests we are looking to change the service account of a SQL mirror implementation. I will be using the same account on all 3 servers involved in the configuration.
I know each server requires the account of the other two adding but as this will be the same account I assume this doesn't apply?
Also for mirrored databases already set up would I need to reconfigure the security for each one?
Is there anything I am missing?
What would be the best process to change the datatype of ntext fields.
I assume just changing the datatype - is not the way to go?
I like to backup the stored procedures' code used in my databases. So I created this Script:
/*
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NOT NULL DROP TABLE [Monitor].[dbo].[Procedurecode];
*/
DECLARE
@db nvarchar(50),
@strSQL nvarchar (100)
IF CURSOR_STATUS('global','cur1')>=-1 BEGIN DEALLOCATE cur1 END
[code]....
Problem is (and I seem not to be the only one with tis) described here:
"If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running." There is nothing magical in SQL server that knows you intend a series of dynamic sql commands to be part of a single job running in a single context. You have to build the entire string for the job you want to execute."
[URL]
So I only get the SPs of the current database.
How do I change the file path from C: drive to E:
See attached
did VARCHAR(20)
SELECT top 10 CAST(did AS INT) FROM table
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
SELECT CONVERT(INT, did) FROM table
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.I found a SQL Function that removes all the alphabetic characters from the string and just leaves the integers.
How can we change the backup replica in Always on Cluster. I am not able to take backup because of this.
View 7 Replies View RelatedWe have 2 node sql 2012 cluster and we have a scenario where domain,IPs,Hostanmes and SQL network name are changing.
1> Can SQL cluster role be brought online by changing the sql network name and its IP once windows team reconfigure the OS cluster or there will be any challenges.
2>should I uninstall sql cluster on both nodes then windows will destroy OS cluster and they then they need to change IP,hostname and domain and then fresh installation of sql cluster
Version: SQL SERVER 2012 enterprise edition and SP2.
Due to one of the security reason, I have changed default port 1433 to another port number in configuration manger tools->protocols for MSSQLSERVER->TCP/IP --> IP ALL section mention another port number.
After restarted service, SQL SSMS able to connect server itself and new port number also LISTENING.
But SQL client SSMS not connected to one of the PC side after changing default port.
I am in process to develop TSql code to identify change in data.
I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.
But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?
select HASHBYTES('SHA','121'+'34'), HASHBYTES('SHA','12'+'134'),BINARY_CHECKSUM('121','34'),BINARY_CHECKSUM('12','134');
in SSIS
( Left(@[User::YourFileName],3) == "AP_" ? 101 : ( Left(@[User::YourFileName],3) == "VD_" ? 102 : ( Left(@[User::YourFileName],3) == "BK_ " ? 103 : 000)))
In the above Variable i want to change the value in Hundreds place i.e from 101,102,103 to 201 ,202, 203 depending upon some Project param
lets say if project param is 1, the values assigned should be as 101,102,103
if project param is 2, the values assigned should be as 201,202,203
if project param is 3, the values assigned should be as 301,302,303 etc...
why when I make a change to a foreach loop container in a SSIS package it does not save?
I am trying to change the base file name and if I change it through either the edit or properties windows, the changes does not save. Fore example i change the Files: or File Spec: from oldfile*.txt to newfilename*.txt and when I save and og bak into the edit area to confirm the change, the Files: section shows as "oldfile*.txt.
I keep getting requests to increase the width of a varchar colum every now and then.
I want to ask if its perfectly ok when you have active users connecting to the application to do this?