I recently had to troubleshoot an ADO.Net connection pool exhaust issue. This problem may indicate a connection leak, but it can also be caused by an undersized connection pool. For analysis I used Windbg. For a quick introduction on how to use Windbg with managed code, see here. Once the problem was reproduced, I took a process dump, copied the dump to my laptop and started digging.

I started by finding a SqlConnection object in the dump. !DumpHeap can be used to dump all objects in all heaps, which is rather verbose. By using the -type filter we can restrict the output to only the objects with type name that contain a given string, and I also restricted to display only the aggregated statistics (-stat) rather than each individual entry:

0:081> !DumpHeap -type System.Data.SqlClient.SqlConnection  -stat
Statistics:
              MT    Count    TotalSize Class Name
00007fff50d4dd90        1           56 System.Data.SqlClient.SqlConnectionPoolGroupProviderInfo
00007fff50d4ed90        1           64 System.Data.SqlClient.SqlConnectionFactory
00007fff50b99cd0        1           80 System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[System.Data.SqlClient.SqlConnectionStringBuilder+Keywords, System.Data]]
00007fff50d4e730        1          216 System.Data.SqlClient.SqlConnectionString
00007fff51348288        1         1440 System.Collections.Generic.Dictionary`2+Entry[[System.String, mscorlib],[System.Data.SqlClient.SqlConnectionStringBuilder+Keywords, System.Data]][]
00007fff5135b5d0       51         1632 System.Data.SqlClient.SqlConnection+<>c__DisplayClass152_0`1[[System.Int32, mscorlib]]
00007fff091c6940      133         4256 System.Data.SqlClient.SqlConnection+<>c__DisplayClass152_0`1[[System.Data.SqlClient.SqlDataReader, System.Data]]
00007fff50d4dd20      100         4800 System.Data.SqlClient.SqlConnectionTimeoutErrorInternal
00007fff50d4cca0      133         6384 System.Data.SqlClient.SqlConnectionPoolKey
00007fff50d507d8      100         9600 System.Data.SqlClient.SqlConnectionTimeoutPhaseDuration[]
00007fff51358b10      377        12064 System.Data.SqlClient.SqlConnection+<>c__DisplayClass114_0
00007fff50d4e838      700        16800 System.Data.SqlClient.SqlConnectionTimeoutPhaseDuration
00007fff50d49ee8      414        89424 System.Data.SqlClient.SqlConnection
Total 2013 objects

From this command I am interested in only one thing, the method table address for the actual System.Data.SqlClient.SqlConnection type. With this I can restrict the !DumpHeap output to only this type objects, using the -mt argument. Notice that there are 414 SqlConnection instances in the dump, although we have a default connection pool size of 100. Lets see them:

0:081> !DumpHeap -mt 00007fff50d49ee8
         Address               MT     Size
000000de8bc9e270 00007fff50d49ee8      216     
000000de8bcf3890 00007fff50d49ee8      216   
...
000000e20e475910 00007fff50d49ee8      216     
000000e20e4dbe18 00007fff50d49ee8      216     
000000e20e5fb6f0 00007fff50d49ee8      216 

If you enabled the DML preference then the output contains hot links to dumping each object with a simple click. This is just a shortcut and you can see the actual Windbg command run by the shortcut click. Lets dump the first object:

0:081> !DumpObj /d 000000de8bc9e270
Name:        System.Data.SqlClient.SqlConnection
MethodTable: 00007fff50d49ee8
EEClass:     00007fff50b9ed10
Size:        216(0xd8) bytes
File:        C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff6069e068  4000577        8        System.Object  0 instance 0000000000000000 __identity
00007fff5f68daa8  4002882       10 ...ponentModel.ISite  0 instance 0000000000000000 site
00007fff5f68f3c0  4002883       18 ....EventHandlerList  0 instance 0000000000000000 events
...
00007fff50d4e968  4000efc       98 ...ConnectionOptions  0 instance 0000000000000000 _userConnectionOptions
00007fff50d4e5d0  4000efd       a0 ...nnectionPoolGroup  0 instance 0000000000000000 _poolGroup
00007fff50d4c080  4000efe       a8 ...onnectionInternal  0 instance 000000e10b4f8418 _innerConnection
00007fff606a03d0  4000eff       b4         System.Int32  1 instance                5 _closeCount
00007fff606a03d0  4000f01       b8         System.Int32  1 instance           368526 ObjectID
...

Those used to native debugging and the eternal chase after symbol files will appreciate the fact that managed data types are self describing even on retail. I removed some clutter, the information I’m after is the _innerConnection. The SqlConnection is just a wrapper. Lets look at it:

0:081> !DumpObj /d 000000e10b4f8418 
Name:        System.Data.ProviderBase.DbConnectionClosedPreviouslyOpened
MethodTable: 00007fff50d32a18
EEClass:     00007fff50bc7ca0
Size:        96(0x60) bytes
File:        C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff606a03d0  4001a5d       38         System.Int32  1 instance                6 _objectID
...
00007fff45d81858  4001a6d       30 ...tions.Transaction  0 instance 0000000000000000 _enlistedTransactionOriginal

OK, so this inner connection is of the type DbConnectionClosedPreviouslyOpened, and I can make an educated guess that this is not relevant for my investigation. Lets look instead at the last SqlConnection object from my list of 414 instances, and its _innerConnection:

0:08>> !DumpObj 000000e20e5fb6f0
Name:        System.Data.SqlClient.SqlConnection
MethodTable: 00007fff50d49ee8
EEClass:     00007fff50b9ed10
Size:        216(0xd8) bytes
File:        C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff6069e068  4000577        8        System.Object  0 instance 0000000000000000 __identity
...
00007fff50d4e5d0  4000efd       a0 ...nnectionPoolGroup  0 instance 000000e08b7e0170 _poolGroup
00007fff50d4c080  4000efe       a8 ...onnectionInternal  0 instance 000000df8c001ab0 _innerConnection
00007fff606a03d0  4000eff       b4         System.Int32  1 instance                1 _closeCount
...

0:081> !DumpObj /d 000000df8c001ab0
Name:        System.Data.SqlClient.SqlInternalConnectionTds
MethodTable: 00007fff50d4c528
EEClass:     00007fff50b9f1b0
Size:        400(0x190) bytes
File:        C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff606a03d0  4001a5d       38         System.Int32  1 instance               95 _objectID
00007fff6068d6f8  4001a60       44       System.Boolean  1 instance                0 _allowSetConnectionString
...
00007fff6069da88  400109d      130        System.String  0 instance 0000000000000000 _routingDestination
00007fff60699ca0  4001089      9b8      System.TimeSpan  1   shared           static _dbAuthenticationContext

This time the inner connection is of type System.Data.SqlClient.SqlInternalConnectionTds, which is more interesting. Before you object that how are you going to find the SqlConnection objects that have inner connection of type SqlInternalConnectionTds, short of searching through all instances, let me say that you don’t have to. All I wanted so far was to do an introduction to the SqlInternalConnectionTds class, which is the one we’re really interested in. I could have started directly with it and ask you to take my word for it,
but I wanted you to see how I came to find this class. So lets look at all the instances of this class, using !DumpHeap -mt ... with this class method table address:

0:081> !DumpHeap -mt 00007fff50d4c528
         Address               MT     Size
000000de8bd10908 00007fff50d4c528      400     
000000de8bd175c8 00007fff50d4c528      400     
000000de8bd2a200 00007fff50d4c528      400     
000000de8bd32a80 00007fff50d4c528      400  
...
000000e20c2d20a0 00007fff50d4c528      400     
000000e20c2f89a0 00007fff50d4c528      400     

Statistics:
              MT    Count    TotalSize Class Name
00007fff50d4c528      100        40000 System.Data.SqlClient.SqlInternalConnectionTds

There are exactly 100 instances in memory, so this really is the class we’re after. It represents an open SqlConnection. So we have a way of finding all active, open SQL connections in memory. What can we do with this information? The SqlInternalConnectionTds type has the _parser field which is an TdsParser instance, and this type has the _physicalStateObj field which is a TdsParserStateObject instance, and this type has the _outBuff field of type Byte[] and I will make an educated guess that this is the last packet sent to the SQL server on this connection:

0:117> !DumpObj /d 000000de8bd10908 
Name:        System.Data.SqlClient.SqlInternalConnectionTds
MethodTable: 00007fff50d4c528
EEClass:     00007fff50b9f1b0
Size:        400(0x190) bytes
File:        C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff606a03d0  4001a5d       38         System.Int32  1 instance              171 _objectID
...
00007fff50d4dd90  4001076       90 ...GroupProviderInfo  0 instance 000000e08b7e0808 _poolGroupProviderInfo
00007fff50d4d580  4001077       98 ...lClient.TdsParser  0 instance 000000de8bd115b0 _parser
...
00007fff60699b20  400109c      178          System.Guid  1 instance 000000de8bd10a80 _originalClientConnectionId
00007fff6069da88  400109d      130        System.String  0 instance 0000000000000000 _routingDestination
00007fff60699ca0  4001089      9b8      System.TimeSpan  1   shared           static _dbAuthenticationContextLockedRefreshTimeSpan
...

0:117> !DumpObj /d 000000de8bd115b0 
Name:        System.Data.SqlClient.TdsParser
MethodTable: 00007fff50d4d580
EEClass:     00007fff50b9f470
Size:        160(0xa0) bytes
File:        C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff606a03d0  4001336       70         System.Int32  1 instance              166 _objectID
00007fff50d4c790  4001338        8 ...ParserStateObject  0 instance 000000de8bd11650 _physicalStateObj
00007fff50d4c790  4001339       10 ...ParserStateObject  0 instance 0000000000000000 _pMarsPhysicalConObj
...
00007fff606a03d0  4001335      8e0         System.Int32  1   shared           static _objectTypeCount
...

0:117> !DumpObj /d 000000de8bd11650 
Name:        System.Data.SqlClient.TdsParserStateObject
MethodTable: 00007fff50d4c790
EEClass:     00007fff50bd7608
Size:        472(0x1d8) bytes
File:        C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
Fields:
              MT    Field   Offset                 Type VT     Attr            Value Name
00007fff606a03d0  400142f      150         System.Int32  1 instance              166 _objectID
00007fff50d4d580  4001430        8 ...lClient.TdsParser  0 instance 000000de8bd115b0 _parser
00007fff50d4ebf0  4001431       10 ...lClient.SNIHandle  0 instance 000000de8bd11f20 _sessionHandle
00007fff60699a20  4001432       18 System.WeakReference  0 instance 000000de8bd11828 _owner
00007fff50d41dc0  4001433       20 ...eader+SharedState  0 instance 0000000000000000 _readerState
00007fff606a03d0  4001434      154         System.Int32  1 instance                0 _activateCount
00007fff606a03d0  4001435      158         System.Int32  1 instance                8 _inputHeaderLen
00007fff606a03d0  4001436      15c         System.Int32  1 instance                8 _outputHeaderLen
00007fff606a27a8  4001437       28        System.Byte[]  0 instance 000000de8bd140c8 _outBuff
00007fff606a03d0  4001438      160         System.Int32  1 instance                8 _outBytesUsed
00007fff606a27a8  4001439       30        System.Byte[]  0 instance 000000de8bd12170 _inBuff
00007fff606a03d0  400143a      164         System.Int32  1 instance               41 _inBytesUsed
00007fff606a03d0  400143b      168         System.Int32  1 instance               41 _inBytesRead
...

0:117> !DumpObj /d 000000de8bd140c8 
Name:        System.Byte[]
MethodTable: 00007fff606a27a8
EEClass:     00007fff600b2348
Size:        8024(0x1f58) bytes
Array:       Rank 1, Number of elements 8000, Type Byte (Print Array)
Content:     ...".............._...E......................4..I.N.S.E.R.T. .[.d.b.o.]...[
Fields:

So it looks like we did found the last command sent on this connection to the SQL Server, but lets dig a bit deeper. Lets dump the Byte[] memory:

0:117> db 000000de8bd140c8
000000de`8bd140c8  a8 27 6a 60 ff 7f 00 00-40 1f 00 00 00 00 00 00  .'j`....@.......
000000de`8bd140d8  0e 11 00 22 00 00 01 00-16 00 00 00 12 00 00 00  ..."............
000000de`8bd140e8  02 00 5f 0a 00 00 45 00-00 00 01 00 00 00 07 00  .._...E.........
000000de`8bd140f8  00 00 02 00 00 00 e7 8c-04 09 04 d0 00 34 8c 04  .............4..
000000de`8bd14108  49 00 4e 00 53 00 45 00-52 00 54 00 20 00 5b 00  I.N.S.E.R.T. .[.

The first 16 bytes are for the Byte[] internal fields, and the actual array starts at 000000de`8bd140d8 (ie. the second line). To understand this packet, we need to understand the TDS protocol. Luckily the protocol is well documented at [MS-TDS]: Tabular Data Stream Protocol. Every packet starts with a packet header and the first byte is the packet type.
In our case the packet type is 0e, which is described in the previous link as a Transaction manager request. So the last operation done with this connection was a request to enlist in a distributed transaction. This behavior is typical of SqlConnection enlisting themselves in lightweight DTC because of a TransactionScope.

With all the knowledge we have so far we can use Windbg script to dump all the live connections and information about the last packet they sent to the server:

I reckon Windbg scripts are a bit arcane in syntax. poi is used to read a pointer value from an address. by reads a single low-order byte from an address. And the magic values like 98, 8 and 28 are the offsets of the relevant fields in the object instance:

• _parser at offset 98:

  00007fff50d4d580  4001077       98 ...lClient.TdsParser  0 instance 000000de8bd115b0 _parser

• _physicalStateObj at offset 8:

  0007fff50d4c790  4001338        8 ...ParserStateObject  0 instance 000000de8bd11650 _physicalStateObj

• _outBuff at offset 28:

  00007fff606a27a8  4001437       28        System.Byte[]  0 instance 000000de8bd140c8 _outBuff

The script uses .foreach(obj {!DumpHeap ... -short}) to iterate through each instance of type SqlInternalConnectionTds in memory, and then for each instance it outputs Debug Markup Language (DML) links to output, in a tabular form, the SqlInternalConnectionTds instance, the _parser, the _physicalStateObj and the _outBuff. It then prints the last sent packet type:

• BTC is a SQL Batch
• RCP is a SQL procedure call (this includes any SqlCommand batch that has parameters)
• BLK is a BULK INSERT operation
• ATN is an attention (a request to cancel an executing command)
• XML is a request to enlist in a DTC

For RPC and Batch requests the script also dumps out the actual SQL command in the request. Note that this is the last packet sent, and if the SQL text was bigger than one packet then the last packet will contain only the ending of the actual command sent. Here is the output from my actual dump I was analyzing:

0:117> $$><c:\temp\dump_all_out_buffs.txt
1 000000de8bd10908 000000de8bd115b0 000000de8bd11650 000000de8bd140c8 XMR
2 000000de8bd175c8 000000de8bd18258 000000de8bd182f8 000000df0bc17180 XMR
3 000000de8bd2a200 000000de8bd2ae90 000000de8bd2af30 000000de8bd2d9a8 XMR
4 000000de8bd32a80 000000de8bd33710 000000de8bd337b0 000000de8bd4e5f8 XMR
5 000000de8bdba120 000000de8bdc1410 000000de8bdc14b0 000000de8bdc3f50 XMR
6 000000de8be0cbb8 000000de8be0d848 000000de8be0d8e8 000000de8be10360 RPC Time], [Var_24].[LastModifierUserId] AS [LastModifierUserId], [Var_24].[CreationTime] AS [CreationTime], [Var_24]...
...
15 000000de8c17ee78 000000de8c19e818 000000de8c19e8b8 000000de8c1a1670 XMR
16 000000de8c18ddf8 000000de8c18ea88 000000de8c274270 000000de8c27e908 RPC SELECT [Extent2].[Name] AS [Name] FROM   (SELECT [Var_20].[UserId] AS [UserId], [Var_20].[RoleId]...
...
90 000000e20be179b8 000000e20be18648 000000e20be186e8 000000e00baecb50 RPC UPDATE [dbo].[Sessions] SET ...
91 000000e20be97138 000000e20be97dc8 000000e20be97e68 000000e20bb526f8 RPC UPDATE [dbo].[Sessions] SET ... 
...
99 000000e20c2d20a0 000000e20c2d2d48 000000e20c2d2de8 000000e20c2ed5b8 XMR
100 000000e20c2f89a0 000000e20c2fe4c0 000000e20c2fe560 000000e20c302620 XMR

You can see all 100 connections in the connection pool, what was the last packet type sent to the server by each connection, and for the applicable cases, the last SQL command executed on that connection.

As an alternative to the cumbersome Windbg script syntax you can try DbgScript, which allows you to use python, ruby, LUA or other scripting languages to control Windbg.

Are INSERTs serialized because of row group locks?

No. If a trickle INSERT requires an OPEN row group and it cannot place a lock on it it will create a new one. any number of INSERT statements can proceed in parallel, each locking its own row group. While this results in lower quality row groups (possible multiple small OPEN row groups) the decision was explicitly to favor concurrency. This applies as well to BULK INSERTs that fail to create the minimum row group size (~100k rows).

Does Tuple Mover delete the rows in deltastore?

No. Surprised? Each OPEN/CLOSED row group has its own individual deltastore. When the Tuple Mover finishes compressing a row group the entire deltastore for that row group will be deallocated. This is done to avoid the explicit logging of each individual row delete from deltastores. This is the same difference as between TRUNCATE and DELETE.

Does Tuple Mover block reads?

No. No way. Absolutely no.

Does Tuple Mover block INSERTS?

No. Both trickle inserts and bulk inserts into clustered columnstores can proceed while the Tuple Mover is compressing a rowgroup. You only need to think about the fact that the Tuple Mover only has business with CLOSED row groups and INSERTs are only concerned with OPEN row groups. There is no overlap so there is no reason for blocking.

Does Tuple Mover block UPDATE, DELETE, MERGE?

Yes. Spooled scans for update (or delete) cannot re-acquire the row if the row storage changes between the scan and the update. So the Tuple Mover is mutually exclusive with any UPDATE, DELETE or MERGE statement on the columnstore. This exclusion is achieved by a special object level lock that is acquired by UPDATE/DELETE/MERGE in shared mode and by the Tuple Mover in exclusive mode.

Can Tuple Mover, or REORGANIZE, shift rows between row groups?

No. The Tuple Mover (and REORGANIZE) can only compress a row group but it cannot shift rows between row groups. Particularly it cannot ‘stich’ several small deltastores into one compressed row group. REBUILD may appear that it can shift or move rows, but REBUILD is doing exactly what the name implies: a full rebuild. It reads the existing columnstore and builds a new one. The organization (row group numbers and size) of the new (rebuilt) columnstore has basically no relation with the original organization of the old columnstore.

One of the most common complains about using Service Broker in production is when administrators discover, usually after some months of usage, that sys.conversations_endpoints grows out of control with CLOSED conversations that are never cleaned up. I will show how this case occurs and what to do to fix it.

Read the rest of this entry »

The SQL Server transaction log contains the history of every action that modified anything in the database. Reading the log is often the last resort when investigating how certain changes occurred. It is one of the main forensic tools at your disposal when trying to identify the author of an unwanted change. Understanding the log and digging through it for information is pretty hard core and definitely not for the faint of heart. And the fact that the output of ::fn_dblog can easily go into millions of rows does not help either. But I’ll try to give some simple practical examples that can go a long way into helping sort through all the information and dig out what you’re interested in.

Read the rest of this entry »

So you have this SQL Server database that your application uses and it somehow seems to be slow. How do you troubleshoot this problem? Where do you look? What do you measure? I hope this article will answer enough questions to get you started so that you can identify the bottlenecks yourself, or know what to search for to further extend your arsenal and knowledge.

• How does SQL Server work
• Wait Info for currently executing requests
• Aggregated wait stats: sys.dm_os_wait_stats
• Common wait types
• Analyze disk activity: IO stats
• Analyzing individual query execution
• Identifying problem queries
• Missing Indexes
• TEMPDB Performance
• SQL Server usage performance counters
• IO related performance counters
• Memory related performance counters
• CPU performance counters
• SQL Server blocking related performance counters
• Network performance counters
• Defining your own counters
• Advanced Performance Analysis
• The USE method

Read the rest of this entry »

Recently I had to investigate a deadlocked Windows Server 2012 machine. Any attempt to start or stop a service on this machine would freeze in an infinite wait. I did not know about the “Analyze Wait Chain” feature in the Task Manager (new since Windows 7), but turns out is quite a life saver. This feature uses the Wait Chain Traversal debugging API and is, on the record, Matt Pietrek’s favourite Windows 7 feature. Simply using the Task Manager I was able to see than many programs were waiting on the “Services and Controller app” service, which is the SCM (the Service Control Manager):

Read the rest of this entry »

If you are a developer writing applications that use SQL Server and you are wondering what exactly happens when you ‘run’ a query from your application, I hope this article will help you write better database code and will help you get started when you have to investigate performance problems.

Requests

SQL Server is a client-server platform. The only way to interact with the back-end database is by sending requests that contain commands for the database. The protocol used to communicate between your application and the database is called TDS (Tabular Data Sream) and is described on MSDN in the Technical Document [MS-TDS]: Tabular Data Stream Protocol. The application can use one of the several client-side implementations of the protocol: the CLR managed SqlClient, OleDB, ODBC, JDBC, PHP Driver for SQL Server or the open source FreeTDS implementation. The gist of it is that when your application whats the database to do anything it will send a request over the TDS protocol. The request itself can take several forms:

Read the rest of this entry »

SQL Server 2012 installation has the potential to leave an msiexec.exe installer process running after the installation finishes, as described in Windows Installer starts repeatedly after you install SQL Server 2012 SP1:

After you install SQL Server 2012 SP1 on a computer, the Windows Installer (Msiexec.exe) process is repeatedly started to repair certain assemblies. Additionally, the following events are logged in the Application log:
EventId: 1004
Source: MsiInstaller
Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’, Component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource”does not exist.

EventId: 1001
Source: MsiInstaller
Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’

When this issue occurs, you experience high CPU usage.
Cause

This issue occurs because the SQL Server 2012 components reference mismatched assemblies. This behavior causes native image generation to fail repeatedly on certain assemblies. Therefore, a repair operation is initiated on the installer package.

But the this problem has a much sinister side effect: it causes growth of the HKLM\Software registry hive. Except for the System hive, all the other registry hives are still restricted in size to a max of 2GB, see Registry Storage Space:

Views of the registry files are mapped in paged pool memory…The maximum size of a registry hive is 2 GB, except for the system hive.

Read the rest of this entry »

I noticed that my database log file has grown to 200Gb. I tried to shrink it but is still 200Gb. How can I shrink the log and reduce the file size?

The problem is that even after you discover about DBCC SHRINKFILE and attempt to reduce the log size, the command seems not to work at all and leaves the log at the same size as before. What is happening?

If you look back at What is an LSN: Log Sequence Number you will see that LSNs are basically pointers (offsets) inside the log file. There is one level of indirection (the VLF sequence number) and then the rest of the LSN is basically an offset inside the Virtual Log File (the VLF). The log is always defined by the two LSNs: the head of the log (where new log records will be placed) and the tail of the log (what is the oldest log record of interest). Generating log activity (ie. any updates in the database) advance the head of the log LSN number. The tail of the log advances when the database log is being backed up (this is a simplification, more on it later).

Read the rest of this entry »

In a previous article Online non-NULL with values column add in SQL Server 2012 I talked about how adding a non-null column with default values is now an online operation in SQL Server 2012 and I mentioned how the situation when the newly added column may increase the rowsize can result in the operation being performed offline:

In the case when the newly added column increases the maximum possible row size over the 8060 bytes limit the column cannot be added online.

In this article I want to show you how such a situation can arise and how it impacts even the case that prior to SQL Server 2012 was always online, namely adding a nullable column. Lets consider the following example:

Read the rest of this entry »