September 5, 2013 at 1:51 am
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.
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
September 5, 2013 at 6:56 am
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
September 5, 2013 at 11:18 pm
By saying "You will get behavior of 2008 R2" are you including all the behaviors or just the IDENTITY property?
September 6, 2013 at 6:16 am
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
September 6, 2013 at 3:46 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 29, 2014 at 3:55 pm
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.
October 29, 2014 at 9:48 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 29, 2014 at 9:55 pm
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.
October 29, 2014 at 10:04 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 14, 2016 at 3:08 am
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,My blog: www.igormicev.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply