Database size limitation in SQL Express

I would generally not blog about this kind of topic, except that my customer recently talked to me about it. According to his tests, he seemed to find that the total size limitation across all databases was 10GB. That did not sound correct to me, because it is quite clearly documented that the 10GB limit is per database.

I quickly tested and found out that the documentation is correct. It is only when an individual database is attempted to be created with a data file beyond the size limit of 10GB, that you get the error:

Msg 1827, Level 16, State 4, Server SOMECOMPUTERSQLEXPRESS, Line 1
CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.

To summarize:

  1. 10GB is the per-database, data-file size limit
  2. If you have more than one data file in your SQL Express database, the size limit is applied to the total size across all such data files
  3. Log files are not subject to this limit

Tracking TEMPDB internal object space usage in SQL 2012

It is a documented fact that using the MAX specifier can result in TEMPDB usage in specific cases. From the TEMPDB whitepaper, some of these can use ‘internal object’ space within TEMPDB:

“all of the large object types: text, image, ntext, varchar(max), varbinary(max), and all others.”

Now, a FAQ from our customers is how to drill down to session level usage of TEMPDB when they suspect abnormal growth. Normally, the sys.dm_db_session_space_usage gives us the details we need. But are there any other options to see deeper? We wanted to test this and observe the effects. To do this, we created a simple stored procedure and a corresponding C# console application which passes in a large valued parameter.

Stored procedure code

create procedure testvarcharmax(@s varchar(max))
as
select @@version

C# application code

Here is the simple front-end application code which invokes the ‘testvarcharmax’ procedure:

SqlConnection cn = new SqlConnection("Data Source=.;initial catalog=tempdb;integrated security=true");
cn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "testvarcharmax";
SqlParameter parameter = new SqlParameter("s", System.Data.SqlDbType.VarChar, -1);
parameter.Value = new StringBuilder().Insert(0, "a", 1000000000).ToString();
cmd.Parameters.Add(parameter);
cmd.CommandTimeout = 360;

cmd.ExecuteNonQuery();

Monitoring TEMPDB

After executing the above C# code, my TEMPDB quickly grew by leaps and bounds. As any DBA would understand, the essential ask in such cases is to trace the session ID which is generating this TEMPDB space usage. One option is to use the sys.dm_db_file_space_usage DMV. That one is useful in this case, as it reports the aggregate space for the internal objects:

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used], (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB] FROM tempdb.sys.dm_db_file_space_usage;

However the above does not provide a session-level detail. To obtain that, you can use the queries on sys.dm_db_session_space_usage and sys.dm_db_task_space_usage as shown below:

select * from tempdb.sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count desc

select * from tempdb.sys.dm_db_task_space_usage
order by internal_objects_alloc_page_count desc

Update: Adam Machanic pointed out that it is much easier and lightweight to use the sys.dm_db_task_space_usage and sys.dm_db_session_space_usage DMVs to track internal object usage to a given task / session. Please use the detailed approach described below only if you have a specific need to investigate lower-level details, such as a potential product issue or such.

Digging deeper

Suppose you suspect a ‘leak’ in TEMPDB objects of some sort, and you need to really peer deep into TEMPDB page allocation / deallocation. To peer more into the internals, KB article 947204 provides an interesting approach using the allocation ring buffer using trace flag 1106. This can be useful in rare cases, but first do note the important disclaimer about this flag:

Note We recommend that you do not use trace flag 1106 on production servers because trace flag 1106 affects the performance of SQL Server.

Note that the query specified therein does not return results on SQL 2012 even after enabling trace flag 1106. It turns out that the query requires a minor change, as the XML schema and the name for the ring buffer has changed in SQL 2012. The new name for the ring buffer is RING_BUFFER_SPACEMGR_TRACE. And here is the modified query, reflecting changes in the XML schema as well:

SELECT top 500
record.value(‘(Record/@id)[1]’, ‘int’) AS record_id,
CONVERT (varchar, DATEADD (ms, -1 * ((inf.cpu_ticks / inf.cpu_ticks) – [timestamp]), GETDATE()), 126) AS EventTime,
[timestamp] ,
  record.value(‘(Record/@id)[1]’, ‘int’) AS RingBuffer_Record_Id,
  record.value(‘(Record/SpaceMgr/Event)[1]’, ‘int’) AS AllocationEventType,
  record.value(‘(Record/SpaceMgr/SpId)[1]’, ‘int’) AS SpId,
  record.value(‘(Record/SpaceMgr/EcId)[1]’, ‘int’) AS EcId,
  record.value(‘(Record/SpaceMgr/PageId)[1]’, ‘nvarchar(50)’) AS AllocatedPageId,
  record.value(‘(Record/SpaceMgr/AuId)[1]’, ‘nvarchar(50)’) AS AllocationUnitId,
  record.value(‘(Record/SpaceMgr/LsId)[1]’, ‘nvarchar(50)’) AS LsId
FROM sys.dm_os_sys_info inf CROSS JOIN (
SELECT timestamp, CONVERT (xml, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_SPACEMGR_TRACE’
AND ( record LIKE ‘%<Event>23</Event>%’ — uniform extent allocation
OR record LIKE ‘%<Event>22</Event>%’ — uniform extent deallocation
OR record LIKE ‘%<Event>24</Event>%’ — mixed extent allocation
OR record LIKE ‘%<Event>25</Event>%’ — mixed extent deallocation
OR record LIKE ‘%<Event>10</Event>%’ — page allocation
OR record LIKE ‘%<Event>11</Event>%’ — page deallocation
)) AS t
    ORDER BY record.value(‘(Record/@id)[1]’, ‘int’) ASC

The results of the ring buffer query are too detailed for most real-time scenarios and the overheads associated with the trace flag make it impractical, as compared to the sys.dm_db_session_space_usage DMV. But if you happen to use the approach in KB 947204 on SQL 2012 for some really low level debugging, please take note of the above changes.

Reference

Queries useful for troubleshooting TEMPDB space usage

Managed Service Accounts (MSA) and SQL 2012: Practical Tips

One of the most common dilemmas for SQL Server administrators is whether they should use AD based domain users as the service accounts, or can they leverage the inbuilt accounts like Network Service etc. If your SQL Server instance is never going to participate in any cross-server contexts (such as availability Groups, Linked Servers, Log Shipping etc.) then you may very well be happy to use one of the inbuilt accounts or even better, in SQL 2012, the special ‘Virtual Account’ feature.

While these options relieve you from the overhead of periodically changing and syncing the service account password, they do impose the machine boundary and cause problems when you want to ‘jump’ across instances. So the classic solution has been to grit one’s teeth and ask for a domain user which will then be configured as the service account. However this brings the hassle of periodic password maintenance, and more importantly that causes downtime.

OR you can configure the SQL 2012 standalone instance to utilize the new Managed Service Accounts feature in Windows 2008 R2 and above. To do this, you follow the steps below.

Setup the MSA in Active Directory

First, create a new MSA in the AD using the PowerShell cmdlet. To do this, there are some simple considerations documented here, but the most important ones are to be an domain administrator, to be on Windows 2008 R2 or above and have the right PowerShell modules installed (Remote Server Administration Tools has a AD PowerShell module which you must install.)

Import-Module ActiveDirectory

New-ADServiceAccount –Name TestSQLMSA -Enabled $true

Next, associate the above MSA with the computer you wish to use it on. Note that a MSA in itself can only be used on one destination computer at a time. In this case, I will specify my lab computer named W2K8R2CN3.

Add-ADComputerServiceAccount -Identity W2K8R2CN3 -ServiceAccount TestSQLMSA

Install the MSA on the target server

Once this is done, you switch to the W2K8R2CN3 computer (which just happens to be a Windows 2008 R2 Core installation) and ‘install’ the MSA on the computer. Now, if you are on Windows 2008 R2 Core installation, to use the ActiveDirectory cmdlets you must effectively install the RSAT-AD-PowerShell feature, but directly using DISM:

DISM /online /enable-feature /featurename=ActiveDirectory-PowerShell

Once that is done, you can easily associate the MSA on the target computer:

Install-ADServiceAccount TestSQLMSA

Important: To do this correctly, you must be a domain administrator. If you run the Install-ADServiceAccount cmdlet and you are not a domain admin, it silently exits, but later when you try to change the service account and start SQL, you will receive an error:

“Error 1069: The service did not start due to a logon failure” and HRESULT 0x8007042d.

If you get those errors, have a domain admin logon to the target server and re-run Install-ADServiceAccount for you.

Change the SQL Service account

Last but not the least: you use SQL Configuration Manager to execute the service account change. An important note is that when specifying the ‘user name’ for the MSA, you must fully qualify it with domain name, and have a trailing $ sign as well. If you do not specify the $ you will receive the error message:

“Invalid parameter [0x80041008]”

As an example, in my test setup, here is how Configuration Manager looks:

image

Supportability questions

MSA and older SQL versions: Though you may be able to configure and / or use MSA with SQL 2008 R2 or older versions, it is officially not tested. If you notice, the documentation for SQL 2008 R2 service accounts is noticeably silent on this topic.

Group Managed Service Accounts (GMSA): As of Feb 2014, Group Managed Service Accounts are NOT officially supported with SQL 2012 Failover Clustered Instances. This is under review by the Product Group for future releases.

References

Please leave your questions, comments below! I’d be very happy to hear from you. Till next time, bye!