SQL 2012 :: Adding T272 Trace Did Not Correct Identity Column 1000 Jump

Jun 15, 2015

After SQL server service restart, a column which is set to auto increment jumped 1000. To fix the issue, I had to add T272 trace flag to SQL startup parameters. However, I did not see the column being reseeded after the service restart, it is still showing the 1000 jump. Am I doing something wrong?

Below the log showing the flag being added to the error log:

LogDateProcessorInfoErrorMSG
2015-06-15 22:29:53.850ServerRegistry startup parameters:
-d E:DATAmaster.mdf
-e E:logERRORLOG
-l E:DATAmastlog.ldf
-T 272

View 3 Replies


ADVERTISEMENT

SQL 2012 :: Impact Of Trace Flag 272 On Server Apart From Disabling Identity Jump

Sep 5, 2013

I am upgrading my application's SQL Server from 2008 R2 to 2012.

As discussed in the below URL I am able to see the Identity jump after I upgrade and the server is restarted.

Now since I cannot afford this and at this moment I do not have the time to create a sequence with NOCACHE and test it again I have to go ahead and add trace flag 272 in the start up parameter as this is the only solution which I can implement and even rollback without much hassles.

[URL] ....

What I got to know, this flag will disable the new feature of IDENTITY property that has been implemented as part of SQL Server 2012 and will make it work like it was doing in SQL Server 2008 R2.

But I want to know implementing this flag would impact any other feature or performance (except the performance of IDENTITY) of SQL Server.

View 8 Replies View Related

SQL 2012 :: Seed Value Of Identity Columns Jumps / Gaps By 1000 After Restarting It?

Jan 9, 2015

We just switched from Sql server 2008R2 to Sql server 2012.I am facing one problem with identity Columns "When ever i restarts my sql server,the seed value for each identity column is increased by 1000 (For int identity column it is 1000 and for big int it is 10000).

"For Example if seed value of any table was 3 then after restarting sql server will be 1003 if i again restart sql server it will be 2003 and so on."

After searching on google i found that it is a new feature (don't know what is use of it) in sql server 2012 and having only two solution if you want old identity concept

1. Use sequence object -

a) I am using same database in sql server 2008 and 2012 both so can't use sequence in 2008.

b) if i go with sequence then need not change save procedure for each table,which is bulky task for us.

2. Use Trace Flag 272 (-T272)

I can go with this solution because there is need not do any changes in my application.Some one suggested me that add -T272 in startup parameter,after this sql server identity column will work normal as previous version.I did the same but it is not working.

I don't want to do any changes in my database structure.

how to use this -T272 or why it is not working.

I don't want to use this new identity feature how to suppress it. Why -T272 is not working.

View 4 Replies View Related

Adding An Identity Column With Out Warnings

Jan 25, 2006

Rao Aregaddan writes "Hi All,

Here is my Query.....

While executing the following code,it is showing some warnings.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Drop procedure [DBO].[Usp_Extract_OLAP_Data_1]
GO

Create Procedure [DBO].[Usp_Extract_OLAP_Data_1] as

begin

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID('[DBO].[processed_olap_data_1]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[processed_olap_data_1]
select * into dbo.processed_olap_data_1 from pubs.dbo.processed_olap_data_1
EXEC ('ALTER TABLE dbo.processed_olap_data_1 ADD generatedid INT IDENTITY')

select * from processed_olap_data_1 order by generatedid

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SET ANSI_WARNINGS OFF
GO

Out Put is as follows
----------------------

(141 row(s) affected)


(141 row(s) affected)

Warning: The table 'processed_olap_data_1' has been created but its maximum row size (3873071) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(141 row(s) affected)


Please guide me on this issue that why iam getting this warning even i set the ansi null and ansi warnings off.
one more thing is that is there any other way to add an
identity column with out using alter statement.and is there any problem with front end guys if it shows like this warnings..."

View 3 Replies View Related

Adding Identity Column Dynamically

Nov 23, 2005

Hi,In my stored procedure I'm doing a SELECT onINFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id onthis table, so I was wondering if it was possible to add it dynamicallyin my SELECT, so that I would assign a unique id to each recordreturned by my SELECT?Thanks for your support.

View 3 Replies View Related

Adding Identity Column To BIG Table

Jul 20, 2005

I've got a table with 36+ million rows. I've been asked to modify thetable and add in an identity column. The code I used caused SQL tolock up and it maxed out the log files. :)The code I used is:Begin TransactionAlter Table ODS_DAILY_SALES_POSADD ODS_DAILY_SALES_POS_ID BigInt NOT NULL IDENTITY (1,1)CommitIs there a way to break up the code? Maybe only do a few millionrecords at a time? Or is there a way to do this without lockinganything up?Thanks,Jennifer

View 2 Replies View Related

SQL 2012 :: Server Trace With Missing Column Statistics?

Jul 23, 2014

Getting events in the default trace saying missing column statistics on a column...

1.The column is the primary key column ( identity )

View 2 Replies View Related

Adding An Identity-based Integer Column

Jul 3, 1999

I need to add a column that has the datatype of integer with a seed and identity increment to a table that already exists and has data in it - and can't all NULLs. I have heard that this not possible if the table already exists.

I have downloaded the demo's of SQL Programmer and a couple of Embarcadero programs to see if that would help, but have had no succes to date.

Thanks for any help,

Alan

View 2 Replies View Related

Adding An Identity Column To Existing Table

Sep 11, 2006

I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.

thx

Kat

View 8 Replies View Related

Transact SQL :: Data Type Auto Identity Is (1,1) But Its Increasing 1000 Each Time

Apr 21, 2015

In my database table has auto Identity file which is (1,1) But Its Increasing 1000 Some time 100 I don't Understand why It is happening in my every table.

View 4 Replies View Related

SQL 2012 :: Gaps In Identity Column

Feb 13, 2014

The identity int column in my sql2012 db is not incrementing properly.

I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:

1, 2, 3, 4, 5, 1004, 1005

This is happening on a random number of tables at very random times...

View 5 Replies View Related

SQL 2012 :: Identity For Characters Column

Mar 9, 2014

We have identity functionality for integer , Similarly i want for characters .i.e.

EX:
ID Name Sal
CD101 A 1000
CD102 B 2000
CD103 C 3000
CD104 D 4000
CD105 E 5000

I want output like above, I wont provide Value for ID column. It it should take automatically while inserting data into table.(like identity column)? Is it possible in SQL Server?

View 9 Replies View Related

SQL 2012 :: Adding Column To A Table Over Linked Server

Apr 22, 2015

I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.

Here is the code:

SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE '
+ @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)

The above Creates this when I expose it via a PRINT statement:

addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'

After I create the statement I use:

EXEC @AlterSQL

And this returns the following error:

Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244
Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.

<hr noshade size='1' width='250' color='#BBC8E5'>

View 1 Replies View Related

SQL 2012 :: Identity Key (unique Column) Not Behaving Properly

Jul 19, 2013

I've seen this error on several of my databases again and again this week:

Violation of PRIMARY KEY constraint 'PK_XXXX'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (whatever number here). The statement has been terminated.

The thing is, the PK is unique, and the INSERT statement does not touch that column, it touches the other ones. So SQL2012 is the one that automatically generates the next and unique available value.

How can be possible that the value to be inserted (generated by SQL2012) is a duplicate one? By the way, this was not happening on the previous SQL2005 or SQL2008 server where the databases were being hosted.

Here's my SQL 2012 version: 11.0.3000.0 (SP1 applied). Runs on top of a Win2008R2 Cluster.

View 9 Replies View Related

SQL 2012 :: Get Identity Column Value When Update Statement Fails

Sep 9, 2015

My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.

However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.

Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.

Now, I was asked to log the specific record where the failure was occured.

Something like log the identity column value or primary key value which record has failed.

View 4 Replies View Related

SQL Server 2012 :: Adding Value To Date Column Caused Overflow

Jun 30, 2015

The script is failing at this point "DATEADD(mm, RowNum, salesdate) subscriptionrowdate" dont know exactly where i am going wrong.

This is my code

SELECT *, CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog, DATEADD(mm, RowNum, salesdate) subscriptionrowdate
FROM (
SELECT viasatsubscriptionid, firstproductregistrationdate, salesdate, baseenddate,
ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum
FROM stage_viasatsubscription
)a

View 9 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

SQL 2012 :: Log File Viewer - Can See Only 1000 Records

Mar 9, 2013

Using the Log file viewer in sql auditing I can see only 1000 record....How can we see more than 1000 records or earlier data...

View 6 Replies View Related

How To Capture The Correct Identity Value

Sep 15, 2006

I have a stored procedure which will do 2 insert statements on 2 different tables. In my 2nd insert statement, I need to know how to capture the exact identity primary key value of the newly inserted record from the first insert statement. I am not sure how to get the correct key value of the new record because there may be more than one user inserting at the same time. Therefore, it is tough to capture the key value that belongs to the user doing his transaction at the time. Please help out. Thanks in advance.blumonde

View 6 Replies View Related

Odd Behavoir With @@identity...not Getting Correct Value

Mar 22, 2001

We are running MS SQL Server 7.0

Target table has single trigger to maintain update time stamp.

We have many copies of the same database schema running on one server, all with unique database names. Such as Test_DB, Development_DB, etc.

This problem only occurs in one database, that was created using a 'restore' of another database.

Problem only happens in a single stored procedure.

Problem:
delete all rows from table (empty table)
Runn dbcc checkident ( 'T_BATCHES', reseed, 0)
Run procedure which does the following:
- Begin transaction
- Insert row into table
- Look at identity value (@@identity) - its value is 2 (*** THIS IS WRONG **)
- Select identity column from table - its value is 1
- Commit transation

The @@identity value being reported is one greater than the actual value in the database.

This does not occur if I do the same steps outside of the stored procedure.
Also, this same code and DDL run in other databases.

Help!

Thanks.

- Brendan

View 4 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.

The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.

The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.

Below is the schema of the three tables

I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.

Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.

This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.

Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.

I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.

Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows

After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.

I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.

DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'

[code].....

View 2 Replies View Related

Transact SQL :: Alter Non Identity Column To Identity Column

Aug 12, 2009

when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.

View 2 Replies View Related

SQL 2012 :: Script To Trace FK Between Different DBs?

Apr 22, 2014

I have this script running to list all FK relationship but it's only for single db,tried to make it work for inter DBs

DECLARE @Tname varchar(40) = 'CustAccount' -- <@>< Table Name to trace
-------------------------------------------------

SELECT DISTINCT No ,
ist1.table_catalog AS [Parent DB],
ist1.table_schema AS ParentSchema,
ParentTable ,
ReferencedColumnName ,
ConstraintName ,
link,
ForeignTable,
ForeignKeyColumn ,

[Code] ....

View 2 Replies View Related

Adding Identity

Oct 19, 2006



how can i change a column attribute using alter table in sql server 2005. i want to add an identity property to my column userid , which deosn't have an identity property defined when created.

i tried this.

alter table userlookup alter column userid int identity(1,1) not null

i got this error:

Incorrect syntax near the keyword 'identity'.

View 6 Replies View Related

Adding Identity

Feb 6, 2008

In design mode of table, when i try to add identity, it is disabled.

These are disabled.
Precision
scale
identity
identity seed
identity increment
is rowguid


why identity is distabled in a table?.


So how can i add identity to a exsiting table?.



View 1 Replies View Related

SQL 2012 :: Trace File Sizes

Jul 30, 2014

I am in the middle of capturing a workload to try and tune a SQL instance and was wondering what kinds of sizes people capture in terms of traces. I am only 1 day into a capture and I believe a typical workload would be a week long capture and I am already at 10GB of files. I am only capturing rpc_completed and sql_batch_completed.

What sizes of workloads do other people capture and then where do you analyse them, do you have particular dedicated server for this kind of thing as at present I am looking to use my local PC. Also what rollover file sizes do people tend to use, I am currently using 1GB.

View 7 Replies View Related

SQL 2012 :: Error Log And Trace File

Sep 2, 2014

Where would i find my particular database Error log file, Event log file and Trace file etc?

View 5 Replies View Related

SQL 2012 :: How To Trace For Parallel Queries

Nov 6, 2015

what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.

MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.

I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in URL.... The BinaryData column says :

0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.- What does "parallel plan running in serial" mean ?

I see a lot of 0x01000000, and a few 0x08000000's in my trace.How can i determine whether one query is hogging CPUs and if reducing it to 4 will work?

View 4 Replies View Related

Adding Identity Fields

Nov 2, 1998

I have a table that I need to add an identity field to. I created a field in the table as an INT and added values to all of the existing records. When I try to change it to an IDENTITY field I continually get an error saying 'Invalid cursor state' . The help function tells me that this is caused by not having enough space in the transaction log but I don't understand this b/c the trans log is configured to expand as needed. Anyone know how I can do this?

Thanks,
Tony

View 2 Replies View Related

SQL 2012 :: Turn Off Server Audit Trace

Mar 21, 2014

Somehow someone turned on a audit on the sql server and it is filling up our hard drive and shutting down sql server eventually. Been trying to google how to shut this audit off but coming up with no via soolution yet. how can I turn this trace off. Each fiel says AuditTrace and date and they happen every other minute. I went into the sql profiler and can pull up the files but how to shut the trace off, it does not say.

View 9 Replies View Related

SQL 2012 :: Deadlock Analysis From Trace File

Sep 10, 2014

I am getting deadlock in my production, i was taken deadlock information from trace file , i found deadlock graph but i am unable to find exact scenario . I am attaching deadlock trace file.

View 5 Replies View Related

SQL 2012 :: Profiler - Trace Direct Queries Only?

Oct 21, 2015

Is there a way to setup a trace to show only direct TSQL statements triggered on my server? note I don't want to capture Procedure calls or the statements called within the procs.

Actually many people are firing direct SQL statements on server. And some are coming from entity framework as well. I just want to capture those.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

Jan 25, 2015

I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.

Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved