Real world story of DBCC PAGE saving the day

Last week I answered a question on Twitter about DBCC PAGE and asked if I could be sent more details about the problem. The person was gracious enough to send me a detailed explanation, and it’s such an interesting scenario that I asked for and received permission to share this cut-down, anonymous version of the story with you.

Basically, it’s a real-world application of using DBCC PAGE to solve a nasty business problem that couldn’t be solved any other way.

The company involved produces government-regulated medical products, where tracking products and their ingredients is absolutely critical to make sure that any problems with materials leads to the affected products being withdrawn to prevent possible loss of life.

The company has an electronic document management application that stores all the tracking documents about all the ingredients, processes, and products. Earlier this year the application started to suffer performance problems, which turned out to be running out of threads in the connection pool. The vendor fix was to reboot. This cleared the problem, but then it started again. Rinse and repeat. After a few cycles of this, the performance problems started appearing more and more frequently after the reboot, leading to lots of complaints.

The DBA got involved and noticed a bunch of blocked sessions, each blocked by unresolved DTC transactions, holding locks on table records. As more records became locked on each reboot, blocking got worse and worse.

The solution? Rollback the DTC transactions.

The problem? These DTC transactions in some cases were days old, and some documents tied to those transactions had already been destroyed. Rolling back the DTC transactions would have meant questionable integrity of large amounts of product inventory, thus endangering patients. It might have been possible to check through everything by hand, but that would have been a prohibitively expensive effort.

DBCC PAGE to the rescue. Using the DMVs, the DBA figured out all the X-locked records and dumped all the associated pages (about 50) with DBCC PAGE dump style 3. By working with the application vendor and the application admin, the information in the records allowed the exact documents involved to be determined, and further querying of the application database confirmed that the documents had indeed been successfully imported. This meant all the hung transactions could be safely terminated.

The ability to look into the pages and figure out which documents were involved saved the company from having to write off a ton of inventory.

Bottom line: undocumented commands are *NOT* just for the intellectual interest of poking about in the internals or for dealing with corruption.

How to tell who changed a log file characteristic?

My good friend Orson Weston (@weston12) posted a question to #sqlhelp on Twitter earlier: Is there a way out of the box to find when and who changed the max file size for a log file?

You can’t tell this from the default trace as that just logs that the database was altered, not the log file.

But yes, I can tell you who made a change to the configuration of the log file for a database, using transaction log analysis.

I just ran the following code to set up the demo:

CREATE DATABASE foo2;
GO
ALTER DATABASE foo2
MODIFY FILE (NAME = N'foo2_log', MAXSIZE = 100GB);
GO

If I want to know who changed the log file properties, I can rely on the fact that the log file is file ID 2, and that the log file characteristics are stored in the sysprufiles hidden system table. Slot 0 in that table is the MDF, slot 1 is the LDF. Using fn_dblog to look in the transaction log for changes to that slot in that table, I can run the following code:

SELECT
	SUSER_SNAME ([Transaction SID]) AS [User],
	[Begin Time] AS [Change Time]
FROM
	fn_dblog (NULL, NULL)
WHERE
	[Transaction ID] =
		(SELECT
			[Transaction ID]
		FROM
			fn_dblog (NULL, NULL)
		WHERE
			[Operation] = N'LOP_MODIFY_ROW'
			AND [AllocUnitName] = N'sys.sysprufiles.clst'
			AND [Slot ID] = 1)
	AND [Operation] = N'LOP_BEGIN_XACT';
GO

 

User                                        Change Time
------------------------------------------- ------------------------
APPLECROSS\paul                             2013/09/18 14:01:25:310

It’s harder to tell exactly what changed, but you can work that out by looking at the structure of the sysprufiles records and matching the Offset column in the log record in the fn_dblog output. Not impossible, but tedious. In most cases it’s enough to know that someone changed the log file characteristics at all.

If you’re interested in a different log file (why do you have more than one?) then figure out which slot ID it is in sysprufiles and substitute that slot ID in the code.

And if you don’t have any information in the current transaction log, you can scan your log backups using the same code and using fn_dump_dblog instead (see here for details, syntax, and a usage warning).

Hope this helps!

New online course: Detecting and Correcting Database Corruption

My latest Pluralsight online training course went live today: SQL Server: Detecting and Correcting Database Corruption

It’s 4 hours long and is the first in a two-part series, with a more advanced course coming in a few weeks.

The modules in this course are:

  • Introduction
  • Causes of Database Corruption
  • Detecting Page Corruption
  • Consistency Checking
  • DBCC CHECKDB and Related Commands
  • Interpreting DBCC CHECKDB Output
  • Simple Restore Techniques
  • Simple Repair Techniques

You can get to the course here.

Pluralsight starts at US$29/month, for more than 80 hours of SQLskills SQL Server training (growing all the time) and more than 770 total developer and IT Pro courses (also growing all the time).

Enjoy!

Death of the MCM program

Last week, in the immediate aftermath of the sudden announcement by Microsoft of the cancelling of the MCM/MCSM (Microsoft Certified Master) program, I kicked off a survey asking how the decision affects you. I hoped it would be one more data point that would help persuade Microsoft Learning (MSL) that the MCM program should continue. Alas, it is not to be, as on Monday’s con-call between the MCM community and MSL it was made very clear that the program is effectively dead.

Although the MCM webpage still says that October 1st is the deadline for taking the exams, for those of you now hurrying to complete the certification, MSL have extended the deadline to December 31st. See Ryan Rinehart’s comment for confirmation of this.

Here are the results of the survey.

MCM Death of the MCM program

The “Other” responses are:

  • 5 x “I had always planned to work towards MCM certification.”
  • 4 x “I’m disappointed, but accept the rationale.”
  • 3 x “I am taking the lab a week from tomorrow. I’d like to be able to retake if i don’t make it!”
  • 3 x “I support ms decision because the design of mcm is wrong though mcm’s purpose is right”
  • 3 x “I’m pretty much tired of all the changes going on, and will probably wait for SQL 2014 certification.”
  • 3 x “Just one more reason why i never pursued certs in the first place. the programs change to frequently”
  • 3 x “no i don’t see this having any direct effect on me but i am disgusted that miscrosoft can treat peoples investment of time and effort so casually.”
  • 1 x “Combination of spent time & money studying and taking the prerequisite certifications because they are prerequisites”
  • 1 x “I am an MCM who was planning to sit the MCA review board and won’t be able to before the program shuts down.”
  • 1 x “I was enrolled for the October rotation.”
  • 1 x “I was waiting for a BI one”
  • 1 x “I’m MCSM: Messaging and had another rotation (DS) scheduled in October”
  • 1 x “stop whining, its not like they are going to change this because of this poll”
  • 1 x “What is MCM?”
  • 1 x “Yes, I have a team of SQL Professionals and MCM is on all of their personal goals. I had 2 due to take this by EoY – one who is stuck between Exam and Lab, with booking engine down. Very disappointed that this prestigious and aspirational Certification is being cancelled”

And there you have it. I feel particularly bad for those people who have spent time and money taking prerequisite exams, or have passed the knowledge exam but not the lab exam.

I made my feelings on the decision pretty clear in the Insider newsletter editorial last week – I’m very sad about the decision, the manner in which it was made, and the manner in which it was communicated – having been deeply involved in the SQL MCM program since the very first Ranger rotation inside Microsoft.

I’m not going to waste your time by reiterating the same arguments about certifications and the MCM program that you’ve probably read several times now over the last two weeks. But I do urge you to read the eloquent post by Joe Sack (who used to run the SQL MCM program) on the subject and the really disappointing con-call with MSL: We can handle the truth.

I wholeheartedly agree with everything Joe says, including the part about being very wary of advocating any future advanced certification that Microsoft comes up with, if they even do – which many of us doubt.

Although the MCM has been removed as the focus point for learning goals, learning still goes on, and people will still aspire to make themselves better SQL Server professionals.

Don’t give up learning – it’s always worth it.

Survey: SQL Server MCM cancellation – does it affect you?

I’m sure most of you have heard the news now that Microsoft has effectively cancelled the MCM/MCSM/MCA program abruptly, with a cold email sent at 10pm last Friday night. There’s a lot of anger in the community about the way it was done – with only one month’s notice for people to attempt the exams before everything closes down October 1st.

The Connect item to bring the certifications back had almost more than 850 up-votes before it was closed and made inaccessible outside of Microsoft (due to abuse from a small handful of people) and there are lots of impassioned comments berating Microsoft for cancelling the program. Plus many people have blogged their thoughts – I liked Jason Brimhall’s post that I linked to in my commentary in yesterday’s SQLskills Insider newsletter.

[Edit: the survey is closed - results are here.]

I think it would be interesting to know just how many are affected by this decision, as I know that many of you have invested time studying for the MCM and money and time attending classes like ours.

And that’s the point of this survey. I’ll editorialize the results next week. You’ll notice there’s only one “No I’m not affected” answer – that’s because I’m only interested in the number of unaffected people.


Thanks!

New classes for 2014 in Tampa, Chicago, Bellevue, Australia open for registration

Kimberly’s been hard at work finalizing details of the classes we’ll be offering in the first half of 2014 and we have 17 new classes open for registration, covering the whole range of courses we teach.

Where are we going?

We’re heading to Tampa, Chicago, and Bellevue in the US, plus Australia (see below) and we may schedule some additional classes in the US later in the year after October, but if we do, it’ll be in Chicago or Bellevue.

Why are you not coming to <insert my city> to teach a class? Well, there are some great reasons for where we’re going:

  • No matter where we teach, 80-90% of students are from somewhere else and travel to the class. There’s a huge benefit to being able to (mostly) disconnect from your life to really immerse yourself in learning.
  • We can’t go everywhere. For those of you who absolutely can’t travel, we have our online courses up on Pluralsight.
  • We’ve used the same great hotels in Tampa, Chicago, and Bellevue since 2011. We know them really well and they know us really well. With these locations we have the class setup and running down to a science, ensuring the smoothest possible experience while you’re learning.

Need Help Justifying Training?

If you need help making the case for training to your organization, we can help you:

Australia!

We’re also coming to Sydney (week of March 10th) and Melbourne (week of March 17th) with IE1 classes in March. We’re working on nailing down the details and we’ll let  you know as soon as we know. Please register interest for these classes through our schedule page and we’ll email you directly when the class details are sorted out. It’ll be 5 years since we taught in Australia last and we’re *really* looking forward to coming back.

Australia will be the only place we take our classes outside of the US in 2014 – as I mentioned back in June, we will not be coming back to London until 2015 or 2016.

Special pricing for past attendees!

All our classes are available to past attendees at 75% of the full price, which is 10% more of a discount than the early-bird prices. Send us an email for details of the discount code to use.

First five registrants in 2013 get an additional seat for free!

For all IE0, IE4, IEHW, IETS, IEDEV, and IEBI classes, the first five people to register at the early-bird price  in 2013 will get to bring a colleague or friend to the class for free. If you want a free seat, go sign up!

Tampa in February 2014

Full details of all class curricula, all event logistics, and registration are on the schedule page.

  • IE1: Immersion Event on Internals and Performance, February 3-7
  • IE0: Immersion Event for the Accidental/Junior DBA, February 3-5
  • IEHW: Immersion Event on SQL Server Hardware, February 6-7
  • IE2: Immerion Event on Performance Tuning, February 10-14
  • IEBI: Immersion Event on Business Intelligence, February 10-14
  • IE3: Immersion Event on High Availablity and Disaster Recovery, February 17-21
  • IEDEV: Immersion Event for Developers, February 17-21

Chicago in May 2014

Full details of all class curricula, all event logistics, and registration are on the schedule page.

  • IE1: Immersion Event on Internals and Performance, April 28 – May 2
  • IEBI: Immersion Event on Business Intelligence, April 28 – May 2
  • IE2: Immerion Event on Performance Tuning, May 5-9
  • IEHW: Immersion Event on SQL Server Hardware, May 5-6
  • IE3: Immersion Event on High Availablity and Disaster Recovery, May 12-16
  • IETS: Immersion Event on Advanced Transact-SQL, May 13-16
  • IE4: Immersion Event on Security, PowerShell, and Developer Support, May 19-23
  • IE0: Immersion Event for the Accidental/Junior DBA, May 19-21

Bellevue in June 2014

Full details of all class curricula, all event logistics, and registration are on the schedule page.

  • IE1: Immersion Event on Internals and Performance, June 9-13
  • IE2: Immerion Event on Performance Tuning, June 16-20

Summary

We’ve really excited with our class line-up – there’s something for everyone – and we’re really looking forward to meeting new people and seeing old friends in 2014. See you there!

If you have any questions at all about the 2014 classes, please send us an email.

Bug: DBCC CHECKDB data purity checks are skipped for master and model

After going back and forth with someone with a corrupt master database, I’ve just discovered a bug. And unfortunately it goes back to SQL Server 2005 so the responsibility is mine for not realizing the problem at the time (but at least it’s not a bug in how my DBCC CHECKDB code works, it’s a bug in how master and model are created).

DBCC CHECKDB performs data purity checks to validate a column’s value is within the legal bounds of the column’s data type. It is supposed to do this for all databases created on SQL Server 2005 onward. This is based on the dbi_dbccFlags field in the database boot page being set to 2.

In all versions, the dbi_dbccFlags field for master and model are set to zero, so no automatic data purity checks are done. I’ve notified the dev team about this and it’s been confirmed as a bug.

Edit 9/4/13: I’ve heard from multiple people who’ve fixed this and found corruption issues in master – excellent! (that they found them, not that they had them :-)

Note: If you’re using Ola Hallengren‘s fabulous free scripts to run your consistency checks, your master and model are set correctly as he always uses WITH DATA_PURITY.

I recommend that you run the following to set the value correctly on all production instances on all versions from SQL Server 2005 onward (this is a one-time operation):

DBCC CHECKDB (N'master') WITH DATA_PURITY, NO_INFOMSGS;
DBCC CHECKDB (N'model') WITH DATA_PURITY, NO_INFOMSGS;
GO

You can check the value for yourself using the following code:

DBCC TRACEON (3604);
DBCC DBINFO (N'master');
GO

Output from SQL Server 2012 SP1:

DBINFO STRUCTURE:

DBINFO @0x000000001E0BD400

dbi_version = 706                   dbi_createVersion = 706             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010008
dbi_crdate = 1900-01-01 00:00:00.000dbi_dbname = master                 dbi_dbid = 1
dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 4000
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)                       dbi_RebuildLogs = 0
dbi_differentialBaseLSN = 0:0:0 (0x00000000:00000000:0000)               dbi_RestoreFlags = 0x0000
dbi_checkptLSN = 324:56:84 (0x00000144:00000038:0054)                    dbi_dbccFlags = 0
dbi_COWLastLSN = 0:0:0 (0x00000000:00000000:0000)

A few seats still available in September classes in WA

There are only a few weeks to go until our September set of classes start in Bellevue, WA and there are still some open seats available.

  • IE1 Immersion Event on Internals and Performance, 9/16-20, is sold out at 36
  • IEHW Immersion Event on SQL Server Hardware, 9/18-19, has several seats left, plus a two-for-one deal – see HERE for details
  • IE2 Immersion Event on Performance Tuning, 9/23-27, has only four seats left – see HERE for details
  • IEo Immersion Event for the Accidental/Junior DBA, 9/30-10/2, has several seats left, plus a two-for-one deal – see HERE for details
  • IETS Immersion Event on Advanced T-SQL, 9/30-10/3, has several seats left – see HERE for details

We hope to see you at one of these events!

Survey: tempdb and transaction log IO latencies

Time for another survey!

This survey’s very simple: run the following code on as many of your production instances as you can, and post a comment with the results or send me plain text/spreadsheet output in an email. Please do not add any more info to the results (like server name, commas, lines) as it’s not relevant for this survey and adds a bunch of time to the results processing.

Results are good until 9/6/13 at least – keep ‘em coming!

SELECT
	[database_id],
	[file_id],
    [ReadLatency] =
		CASE WHEN [num_of_reads] = 0
			THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
	[WriteLatency] =
		CASE WHEN [num_of_writes] = 0
			THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END
FROM
	sys.dm_io_virtual_file_stats (NULL, NULL)
WHERE
	[file_id] = 2 OR [database_id] = 2;
GO

 

database_id file_id ReadLatency          WriteLatency
----------- ------- -------------------- --------------------
1           2       0                    2
2           1       0                    6
2           2       0                    1
3           2       7                    9
4           2       10                   0
5           2       1                    1
7           2       6                    1
8           2       15                   55
9           2       13                   59
10          2       0                    0
11          2       44                   8


I’ll editorialize the results in a couple of weeks.

Thanks!

More than half of all SQL Server instances are out-of-date and unsupported!

Back in May I started a survey to collect build numbers to see how up-to-date people keep their SQL Server instances and I’ve been putting off writing this post as I wanted to do justice to the data.

I received results for 3085 SQL Server instances from several hundred respondents – thank you! I broke down the results from @@VERSION using some T-SQL code and then used information from http://sqlserverbuilds.blogspot.com/ to figure out what each build number corresponded to. I also had a bunch of pointers to information on what’s supported and what’s not from my colleague Glenn Berry - thanks Glenn!

Here are the summarized results:

Release   Instances    Survey %    Out-Of-Date %    Unsupported %
----------------------------------------------------------------------------------
7.0       1            0.03%       100%             100%
2000      164          5.32%       82.93%           100%
2005      1285         41.65%      67.08%           67.08%
2008      394          12.77%      41.12%           41.12%
2008 R2   1058         34.29%      43.48%           43.48% (25% more from October)
2012      182          5.90%       24.73%           0% (24.73% more from November)

My definition of “out-of-date” is basically not being on the latest SP for the release. I know that many people do not like updating with every Cumulative Update so it was hard to make any more finessed decision than that. My definition of “Unsupported” is based on the Microsoft Support site for each release, which I’ve linked to in the detailed results below.

I think the results are quite amazing.

51% of all instances in the survey are out of date, by at least not being on the latest SP build, and 53% of all instances in the survey are unsupported.

That’s just incredible. Granted that my results are only from several hundred people, but I’m going to go out on a limb and say I believe this is representative of the state of the installed SQL Server base.

I urge you all to check your SQL Server instances to make sure they’re running as close to the latest build as possible, so you have the most bug fixes and are supported if you do have a problem. I realize that there are many reasons to still be running an older version of SQL Server (e.g. my post You guys still use SQL Server 2000? Really? that discusses application certification) but at least be running the most recent Service Pack or Cumulative Update if you’re able to!

I’m still amazed at the results. I hope you find this information useful.

The detailed results are below.

SQL Server 7.0

All SQL Server 7.0 builds are unsupported.

Data for 1 SQL Server 7.0 instance (0.03% of results):

How Many   Build Number     Release
------------------------------------------------------
1          7.00.841         7.0 SP2

The latest  build available for 7.0 is 7.00.1152, which is 7.0 SP4 + MS08-040, so 100% of 7.0 instances in the survey are out-of-date and are unsupported by Microsoft.

SQL Server 2000

All SQL server 2000 builds are unsupported as of April 9th, 2013 (see MS Support site here).

Data for 164 SQL Server 2000 instances (5.32% of results):

How Many   Build Number     Release
------------------------------------------------------
14         8.00.760         2000 SP3
5          8.00.818         2000 SP3 + MS03-031
16         8.00.2039        2000 SP4
5          8.00.2040        2000 SP4 + KB899761
3          8.00.2050        2000 SP4 + MS08-040
13         8.00.2055        2000 SP4 + MS09-004
15         8.00.2066        2000 SP4 + MS12-060
10         8.00.2187        2000 SP4 CU1
1          8.00.2246        2000 SP4 CU1 + KB935465
37         8.00.2273        2000 SP4 CU1 + MS08-040
27         8.00.2282        2000 SP4 CU1 + MS09-004
5          8.00.2301        2000 SP4 CU1 + MS12-027
13         8.00.2305        2000 SP4 CU1 + MS12-060

MS12-060 addresses a critical security issue, so instances on 8.00.2066 or 8.00.2305 I’ll consider up-to-date. That means that only 28 (17.1%) of the 200o instances in the survey are up-to-date, and all of them are unsupported by Microsoft.

SQL Server 2005

All SQL Server 2005 builds are out of mainstream support and have extended support until April 12th, 2016 (see MS Support site here).

Data for 1285 SQL Server 2005 instances (41.65% of results):

How Many   Build Number     Release
------------------------------------------------------
7          9.00.1399.06     2005 RTM
1          9.00.2040.00     2005 SP1 CTP1
5          9.00.2047.00     2005 SP1
1          9.00.2153.00     2005 SP1 CU1
37         9.00.3042.00     2005 SP2
1          9.00.3050.00     2005 SP2 + KB933508
1          9.00.3054.00     2005 SP2 + KB934458
1          9.00.3073.00     2005 SP2 + MS08-052
7          9.00.3080.00     2005 SP2 + MS09-062
2          9.00.3159.00     2005 SP2 + KB924459
1          9.00.3200.00     2005 SP2 CU4
2          9.00.3233.00     2005 SP2 CU4 + MS08-040
12         9.00.3282.00     2005 SP2 CU9
2          9.00.3310.00     2005 SP2 CU9 + MS09-004
71         9.00.3353.00     2005 SP2 CU15 + MS09-062
192        9.00.4035.00     2005 SP3
102        9.00.4053.00     2005 SP3 + MS09-062
40         9.00.4060.00     2005 SP3 + MS11-049
10         9.00.4211.00     2005 SP3 CU2
120        9.00.4262.00     2005 SP3 CU2 + MS09-062
20         9.00.4266.00     2005 SP3 CU6
106        9.00.4273.00     2005 SP3 CU7
4          9.00.4285.00     2005 SP3 CU8
1          9.00.4305.00     2005 SP3 CU10
116        9.00.4340.00     2005 SP3 CU15 + MS11-049
252        9.00.5000.00     2005 SP4
144        9.00.5069.00     2005 SP4 + MS11-049
10         9.00.5266.00     2005 SP4 CU3
1          9.00.5292.00     2005 SP4 CU3 + MS11-049
16         9.00.5324.00     2005 SP4 CU3 + MS12-070

MS12-070 (Reporting Services) and MS11-049 (XML editing) are not critical updates unless that area of SQL Server is being used, so I consider anyone on 2005 SP4 onward as up-to-date. This means that only 423 (32.9%) of the 2005 instances in the survey are up-to-date and supported, as only SP4 is in Extended Support.

SQL Server 2008

Any SQL Server 2008 build before SP3 is unsupported. Mainstream support ends on July 8th, 2014, with extended support available until July 9th, 2019 (see MS Support site here).

Data for 394 SQL Server 2008 instances (12.77% of results):

How Many   Build Number     Release
------------------------------------------------------
5          10.0.1600.22     2008 RTM
39         10.0.2531.0      2008 SP1
5          10.0.2573.0      2008 SP1 + MS11-049
2          10.0.2723.0      2008 SP1 CU3
2          10.0.2734.0      2008 SP1 CU4
3          10.0.2746.0      2008 SP1 CU5
1          10.0.2757.0      2008 SP1 CU6
2          10.0.2789.0      2008 SP1 CU9
6          10.0.2799.0      2008 SP1 CU10
58         10.0.4000.0      2008 SP2
15         10.0.4064.0      2008 SP2 + MS11-049
2          10.0.4067.0      2008 SP2 + MS12-070
1          10.0.4272.0      2008 SP2 CU2
15         10.0.4311.0      2008 SP2 CU4 + MS11-049
2          10.0.4316.0      2008 SP2 CU5
2          10.0.4333.0      2008 SP2 CU11
2          10.0.4371.0      2008 SP2 CU11 + MS12-070
169        10.0.5500.0      2008 SP3
8          10.0.5512.0      2008 SP3 + MS12-070
1          10.0.5766.0      2008 SP3 CU1
6          10.0.5770.0      2008 SP3 CU3
10         10.0.5788.0      2008 SP3 CU6
22         10.0.5826.0      2008 SP3 CU7 + MS12-070
9          10.0.5828.0      2008 SP3 CU8
1          10.0.5829.0      2008 SP3 CU9
5          10.0.5835.0      2008 SP3 CU10

MS12-070 (Reporting Services) is not a critical update unless that area of SQL Server is being used, so I consider anyone on 2008 SP3 onward as up-to-date. This means that 232 (58.88%) of the 2008 instances in the survey are up-to-date and supported, as only SP3 is supported.

SQL Server 2008 R2

SQL Server 2008 R2 RTM builds are unsupported, and support for SP1 builds ends on October 10th, 2013. Mainstream support of SP2 builds ends on July 8th, 2014, with extended support available until July 9th, 2019 (see MS Support site here).

Data for 1058 SQL Server 2008 R2 instances (34.29% of results):

How Many   Build Number     Release
------------------------------------------------------
167        10.50.1600.1     2008 R2 RTM
21         10.50.1617.0     2008 R2 RTM + MS11-049
3          10.50.1746.0     2008 R2 RTM CU4
1          10.50.1753.0     2008 R2 RTM CU5
1          10.50.1797.0     2008 R2 RTM CU8
2          10.50.2425.0     2008 R2 RTM CU14 + unknown
168        10.50.2500.0     2008 R2 SP1
26         10.50.2550.0     2008 R2 SP1 + MS12-070
37         10.50.2769.0     2008 R2 SP1 CU1
2          10.50.2772.0     2008 R2 SP1 CU2
3          10.50.2796.0     2008 R2 SP1 CU4
14         10.50.2806.0     2008 R2 SP1 CU5
5          10.50.2811.0     2008 R2 SP1 CU6
3          10.50.2817.0     2008 R2 SP1 CU7
5          10.50.2861.0     2008 R2 SP1 CU8 + MS12-070
2          10.50.2868.0     2008 R2 SP1 CU10
518        10.50.4000.0     2008 R2 SP2
3          10.50.4260.0     2008 R2 SP2 CU1
10         10.50.4263.0     2008 R2 SP2 CU2
10         10.50.4266.0     2008 R2 SP2 CU3
19         10.50.4270.0     2008 R2 SP2 CU4
34         10.50.4276.0     2008 R2 SP2 CU5
4          10.50.4279.0     2008 R2 SP2 CU6 (replaced by build 4285)

I consider anyone on 2008 R2 SP2 onward as up-to-date. This means that 598 (56.52%) of the 2008 R2 instances in the survey are up-to-date and supported, with a further 265 (25.05%) already out-of-date instances going unsupported in October.

SQL Server 2012

All builds of SQL Server 2012 are currently supported, but support for RTM builds will end on November 7th, 2013 as that will be one year form the SP1 release (see MS Support site here).

Data for 182 SQL Server 2012 instances (5.90% of results):

How Many   Build Number     Release
------------------------------------------------------
38         11.0.2100.60     2012 RTM
1          11.0.2218.0      2012 RTM + MS12-070
1          11.0.2332.0      2012 RTM CU3
1          11.0.2376.0      2012 RTM CU3 + MS12-070
1          11.0.2383.0      2012 RTM CU4
1          11.0.2395.0      2012 RTM CU5
1          11.0.2405.0      2012 RTM CU7
33         11.0.3000.0      2012 SP1
19         11.0.3128.0      2012 SP1 + KB2793634
6          11.0.3321.0      2012 SP1 CU1
53         11.0.3339.0      2012 SP1 CU2
21         11.0.3349.0      2012 SP1 CU3
2          11.0.3350.0      2012 SP1 CU3 + KB2832017
3          11.0.3368.0      2012 SP1 CU4

I consider anyone on 2012 SP1 onward as up-to-date. This means that 137 (75.27%) of the 2012 instance in the survey are up-to-date, and all instances are supported.