Category: SQL 2012

Change Tracking Cleanup Limitation

I’ve been “fortunate” enough to be working a great deal with Change Tracking recently and have run into a limitation that it’s worth keeping an eye out for on your servers.

If you have a database, with Change Tracking enabled, and the total number of transactions exceeds 14.4 million for all of the tables that are tracked in that database then you are going to be in for some hurt.

The problem is that the process to clean up the CT data in the (internal) syscommittab table only runs once a minute, and only cleans up a maximum of 10,000 rows every time it runs.

10000 (rows) x 60 (minutes an hour) x 24 (hours a day) = 14,400,000

Given that it cannot clear faster than that limitation the internal table will continue to grow over time and it will cause other cleanup processes to slow down, as well as your CT queries. This is definitely an issue in SQL 2012, I have no yet checked other versions.

Here’s a quick screen grab where you can see the cleanup and the number of rows cleaned up:

download (7)


You can see if you are running into this limitation by checking the age of the earliest syscommittab row and seeing if it exceeds your retention time

select datediff(hour, commit_time, getutcdate()) 
from sys.dm_tran_commit_table 
where commit_ts = (select min(min_valid_version) 
  from sys.change_tracking_tables)


You can also check the size and rowcount of the syscommittab table to gauge if it’s something you should be concerned with

selectt.Name as CTName
, sum(p.rows) as ChangeTrackingRows
, convert(numeric(10,2),(sum(u.total_pages)*8)/1024.0) as SizeMB
from sys.internal_tables t
join sys.partitions p on t.object_id = p.object_id
join sys.allocation_units u on (p.hobt_id = u.container_id OR p.partition_id = u.container_id)
left join sys.change_tracking_tables c on t.parent_object_id = c.object_id
where = 'syscommittab'
group by


I have a whole bunch more stuff coming on Change Tracking (including an undocumented workaround for this issue), but for now this is important to know.

You Can’t Meet Your RPO/RTO With AlwaysOn

“That title may have caught your attention. AlwaysOn is the future of HA/DR for SQL Server, and has been since the release of SQL 2012.  

AlwaysOn is actually a marketing term which covers Failover Cluster Instances (FCIs) and Availability Groups (AGs). Allan Hirt (@sqlha | blog) is a strong proponent of ensuring that people understand what this actually means. So much so that he even ranted about it a little

I’ve used FCIs for years, going back to the active/passive clustering days of old, and I’ve used Availability Groups in the last few months. They are both great, and both have limitations: FCIs with their shared storage and AGs with some network and quorum oddities. 

Both of them will do a fine job for you if you have the time, patience, and in the case of AGs, money to get them up and running. They still will not allow you to meet your RPO/RTO though. 

Critical to your business and your users is your up time, and that’s where the Recovery Time Objective (RTO) and Recovery Point Objective (RPO)  come into play. They reflect amount of time it will take to get your services back up and running, as well as the level of data loss that you are willing to accept.



Where FCI/AG win

The key problem with FCI/AG is that they do everything that they can to ensure that transactions are kept as up to date as possible. With FCI you move an entire instance over to another node, everything committed goes with it. With AGs the log records are shipped to the secondaries and applied in a synchronous or asynchronous fashion. The asynchronous setting is designed to get transactions there as soon as possible, and great for longer distances or where low commit times are ultra-critical. Both of these solutions solve two problems…a hardware issue or a software issue.

What does that mean? If your server goes down, then you can failover and lose next to nothing and be back up and running quickly. If Windows goes out to lunch on one of the machines then you can failover and keep ticking along.

So where do they fall down? 



What FCI/AG cannot do

Let’s say there’s a code release and a table accidentally has an update run against it with no where clause. All of a sudden you have a table in a 500GB database which contains 3 million rows and all of the information is wrong. Your users cannot use the application, your help desk is getting call after call and you are stuck. 

Your only option here is to restore your backup and roll up your transaction logs to the point right before the update happened. You’ve done tests on this and know that it will take 120 minutes to get back to that point. Now you have a 2 hour outage and users are screaming, the CIO is at your desk wondering how this happened, and demanding you get the database back up sooner.

FCIs and AGs are not going to help you in this situation. That update is already committed and so failing over the instance won’t help. The transaction logs were hardened immediately on your synchronous partner and applied within 5 seconds on your asynchronous target.

So how has AlwaysOn helped you in this situation? It hasn’t. And while you can sit there cussing out Microsoft for pushing this solution that has this massive failing it’s not going to solve your problem. That’s why you need something more than AlwaysOn. 



You can pry Log Shipping from my cold dead hands


 “Log Shipping?” I hear you ask, “but that’s so old.”

It sure is. It’s old, it’s clunky, and it is perfect for the scenario I just mentioned.  

You can configure log shipping to delay writing transaction logs to remote servers. Let’s say you delay logs for 1 hour. That accidental mass update was performed, you realize that you are in trouble. You quickly apply the logs on the secondary to the point in time before the update, bring the database online and repoint your clients. You are back up again in 5 minutes. It’s a momentary issue. Sure, you have an outage, but that outage lasts a fraction of the time. Your help desk is not inundated with calls, your users aren’t left out in the cold for hours. 

There’s nothing to say that you have to delay applying those logs for an hour. It could be 2 hours, or even 24. It really all depends on how you want to handle things. 

Sure, you have to do manual failover, and you don’t have the ability for automatic page level restores from one of the synchronous AG secondaries, but you have a level of data resiliency that AlwaysOn does not provide you. 


So while AlwaysOn technologies are great, and you should absolutely use them to enhance HA/DR in your business, but you have to be aware of their limitations, and be sure to use other parts of SQL Server to ensure that you can keep your business running. 

SQL2012 It’s The Small Things Pt4–User Defined Server Roles

Server roles have been around since before I started working with SQL Server however these roles have always been strictly defined and there has been no ability to either alter them or create new ones. SQL Server 2012 finally changes that and provides you with the opportunity to create server level roles that will let you provide greater permissions to logins without having to constantly deal with the minutiae of managing them on a login by login basis.

Let’s say for example you wanted to grant several logins the ability the view any definition. Normally this would require you to perform those grants on a very granular level. Now you can just create a server role and drop logins into that role:

USE [master]






use [master]







ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsqlBiggles];


That’s some quick and easy management.

Here’s some examples of other permissions that can be quickly and easily provisioned to multiple users via user defined server roles:

  • Alter any linked server
  • Alter any login
  • Alter any server audit
  • Alter any server role
  • Alter resources
  • Alter server state
  • Alter settings
  • View any database
  • Shutdown SQL Server

It goes beyond basic SQL Server permissions, you can also allow server roles to impersonate logins or manage Availability Groups

USE [master]






use [master]






ALTER SERVER ROLE [SrvImpersonate] ADD MEMBER [sirsqlBiggles];


Anything that allows me to manage SQL Server more efficiently makes me happy. User defined server roles certainly make me happy.

SQL2012 It’s The Small Things Pt3–End Of The Month Function

If you work with financial data or have to do regular reporting type functions then I’m sure you currently use some kind of funky function/case statement to figure out what the last day of the month is and something even more funky to find the last day of next month or the month after.

SQL Server 2012 fixes that for you with the introduction of the EOMONTH function.

It’s very simple to use:

SELECT EOMONTH('2012/03/16')



Even better, the EOMONTH function can accept an additional parameter where you can specify a number of months to add or remove to get relevant results:

SELECT EOMONTH('2012/03/16', -1)



It even handled the leap year correctly.

Simplicity itself. A great addition.

SQL2012 It’s The Small Things Pt2–Unified Security Experience

Back when SQL Server 2005 came out there was a big change to the way that logins were handled. No more stored procedures to create a SQL login or to allow a Windows user or group to connect to SQL. It also brought the advent of allowing SQL logins to have the same kind of policy restrictions as you had in Active Directory.

Despite these huge changes for some reason Microsoft did not implement the same kind of changes when it came to roles. You still had to use stored procedures to grant role membership, this lead to an experience where you had to manage the syntax differently:

USE Master;



USE tempdb;




exec sp_addrolemember 'RoleTest', 'sirsqlDBAs';

exec sp_droprolemember 'RoleTest', 'sirsqlDBAs';


With SQL 2012 this old stored procedure way of managing things has finally gone by the wayside and we now manage roles in a similar fashion to logins:

USE Master;



USE tempdb;







This doesn’t just work with database roles. Also gone is the sp_addsrvrolemember syntax (which for some reason had you specify the login first then the role, opposite how sp_addrolemember did).




Managing roles this way makes a lot more sense than trying to do things via stored procedures. In an effort to ensure that old code still works Microsoft have left the old stored procedures in, but deprecated them, so don’t expect them to work in the next release. Anyway, wouldn’t you rather work to retool your code so that your security experience is more unified?

SQL2012 It’s The Small Things–Default Schemas For Groups

If you’re reading this then I’m sure you know all about SQL 2012 which just went RTM and will be available for general release on April 1st. I’m sure you’ve also heard all about some of the big new changes such as AlwaysOn with readable secondaries, ColumnStore indexes, and FileTables. Missed in all the big announcements are some of the smaller things that make a huge difference in the day to day running of SQL Server. I’m going to put together a few posts outlining some of the smaller, but to me equally significant, changes that are in the new version.


Back in the day there was no facility to provide a default schema to a Windows Group, you would actually get an error were you to try:

USE Master;



USE tempdb;



ALTER USER [sirsqlDBAs] with DEFAULT_SCHEMA = dbo;




This script now works in SQL 2012 and defines the default schema



If we check out the properties of the user we can see that the default schema has been set to dbo:




This is a very welcome addition. Prior to SQL 2012 you would have to create a separate login and user for each user in a windows group and then set the default schema for each, that’s a lot of administrative hassle eliminated.

Please Don’t Use Deprecated Data Types

I know that a lot of vendors like to write for the lowest common denominator (i.e. SQL 2000) but really folks it’s gone too far. I’m sick of cracking open vendor code that’s certified for SQL 2008 and seeing things like IMAGE and TEXT data types. Microsoft deprecated these things back when they released SQL 2005 (see under Textpointers). Why are you persisting these things six years later?

I bring this up because I’ve come across further egregious usage of these data types in vendor code yet again. The vendor in question? Microsoft.

Yes, that’s right, the folks that deprecated the data type six years ago is still using it to a large extent within the ReportServer and ReportServerTempDB databases that support SQL Server Reporting Services. Seriously Microsoft? Can you please get with the plan and fix this nonsense?

The following query, run against the ReportServer database will show 14 different tables (31 columns) using a variety of NTEXT and IMAGE data types.

select as TableName, as DataType, as ColumnName 

    from sys.types t 

        inner join sys.columns sc

            on t.system_type_id = sc.system_type_id

        inner join sys.tables st

            on sc.object_id = st.object_id

where in ('image', 'text', 'ntext')

order by,


I have filed a Connect item asking Microsoft to fix this. Please go vote for it at and help us rid the world of this old stuff.