SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Impact of trace flag 272 on SQL Server 2012 apart from disabling identity jump


Impact of trace flag 272 on SQL Server 2012 apart from disabling identity jump

Author
Message
ghoshal.soumyadeb
ghoshal.soumyadeb
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 72
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=http://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity][/url]

I have searched a lot but nowhere I found any kind of documentation around this flag. What I got to know by reading several web literatures is, 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, that I can bear with) of SQL Server.

Thanks
Soumyadeb
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12224 Visits: 8540
I doubt very much you will find any benchmarking or other documentation on this trace flag. It would seem to be a "mostly-unsupported/undocumented" TF. I doubt you have time to do your own testing either, so you will just have to hope that "you will get the behavior from 2008R2".

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
ghoshal.soumyadeb
ghoshal.soumyadeb
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 72
By saying "You will get behavior of 2008 R2" are you including all the behaviors or just the IDENTITY property?
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12224 Visits: 8540
ghoshal.soumyadeb (9/5/2013)
By saying "You will get behavior of 2008 R2" are you including all the behaviors or just the IDENTITY property?


I'm not saying anything. That was just a quote from the referenced link you had. I would take it to mean what it says.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2093 Visits: 872
Personally, I would not care about the trace flag at all and I don't understand why Microsoft bothered implementing it. The point with IDENTITY is that is a meaningless number which you at most can trust to be monotonically increasing. If you care about the values in it, you should not use IDENTITY values at all.

In any case, using the trace flag means that you will not get the benefit of the caching that was implemented in SQL 2012, and which can matter in scenarios with a high insert-ratio.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
russc-993845
russc-993845
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 8
Erland,

I understand that the identity value is "meaningless", but in previous version of MSSQL it provided me with an accurate enough incremental number to allow for accurate auditing. In over 5 years, I have millions of e-commerce transactions and have only had 11 identity numbers not be sequential. 5 of them were deletions, so 6 were non-committed transactions. For my use, that was accurate enough and I'm sure there are many other developers who used it the same way.

This might not be the "perfect" solution, but for me is was simple and accurate enough for what I needed. MS changes have a big impact on my application. They should provide more documentation on TF272, it's impact, etc. I have even read that it requires a lower case t, though some say it works with upper case.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2093 Visits: 872
russc-993845 (10/29/2014)
I understand that the identity value is "meaningless", but in previous version of MSSQL it provided me with an accurate enough incremental number to allow for accurate auditing. In over 5 years, I have millions of e-commerce transactions and have only had 11 identity numbers not be sequential. 5 of them were deletions, so 6 were non-committed transactions. For my use, that was accurate enough and I'm sure there are many other developers who used it the same way.


You had luck. Sometimes it is unfortunate to have luck, because it may lead to that you become dependent on an inapproriate solution.

They should provide more documentation on TF272, it's impact, etc. I have even read that it requires a lower case t, though some say it works with upper case.


There is no lower- or uppercase for digits. :-) If you are thinking of the startup option to set the trace flag, this option is documented in Books Online, and it is -T.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
russc-993845
russc-993845
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 8
Whatever luck you want to call it, it has worked for me through at least 6 versions of SQL. Judging by the number of posts across the net from others complaining about the same issue, I'm not the only lucky one! ;-)

Yes, I meant the startup option - sorry I wasn't clear. There are multiple posts from people stating the upper case T doesn't work - though some said it did. I haven't tested it yet though I will be this weekend.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2093 Visits: 872
russc-993845 (10/29/2014)
Whatever luck you want to call it, it has worked for me through at least 6 versions of SQL. Judging by the number of posts across the net from others complaining about the same issue, I'm not the only lucky one! ;-)


There is more than one situation where people has used improper solutions, which has seemed to work, but where there never were any guarantees.

Yes, I meant the startup option - sorry I wasn't clear. There are multiple posts from people stating the upper case T doesn't work - though some said it did. I haven't tested it yet though I will be this weekend.


What is important is that there must be no space. That is, it should be -T272.

There is all reason to consider implementing solution where have control over how the numbers are generated. That 1000-id gap was an accident that was waiting to happen all along the way.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5814 Visits: 5079
Erland Sommarskog (10/29/2014)
russc-993845 (10/29/2014)
Whatever luck you want to call it, it has worked for me through at least 6 versions of SQL. Judging by the number of posts across the net from others complaining about the same issue, I'm not the only lucky one! ;-)


What is important is that there must be no space. That is, it should be -T272.

No, -T272 doesn't work. It must be -t272. Lower "t" only for trace flag 272. I've experienced this on two instances running on clustered environments (AlwaysOn and Shared storage).
If you don't believe me, then double check it here.
http://sqlblog.com/blogs/kalen_delaney/archive/2014/06/17/lost-identity.aspx

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search