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: Wednesday, June 4, 2014 12:22 AM
Points: 2, Visits: 63
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: Yesterday @ 8:36 AM
Points: 4,319, Visits: 6,112
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: Wednesday, June 4, 2014 12:22 AM
Points: 2, Visits: 63
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: Yesterday @ 8:36 AM
Points: 4,319, Visits: 6,112
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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 786, Visits: 690
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse