Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Impact of trace flag 272 on SQL Server 2012 apart from disabling identity jump Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 1:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 5:49 AM
Points: 2, Visits: 67
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
Post #1491658
Posted Thursday, September 5, 2013 6:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 4,435, Visits: 6,335
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 at GMail
Post #1491762
Posted Thursday, September 5, 2013 11:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 5:49 AM
Points: 2, Visits: 67
By saying "You will get behavior of 2008 R2" are you including all the behaviors or just the IDENTITY property?
Post #1492036
Posted Friday, September 6, 2013 6:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 4,435, Visits: 6,335
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 at GMail
Post #1492186
Posted Friday, September 6, 2013 3:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 811, Visits: 738
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
Post #1492445
Posted Wednesday, October 29, 2014 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 9:49 PM
Points: 2, Visits: 6
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.
Post #1629665
Posted Wednesday, October 29, 2014 9:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 811, Visits: 738
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
Post #1629691
Posted Wednesday, October 29, 2014 9:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 9:49 PM
Points: 2, Visits: 6
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.
Post #1629693
Posted Wednesday, October 29, 2014 10:04 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 811, Visits: 738
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
Post #1629696
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse