sql-server-2016
The suspect table has 299 nvarchar(max) columns, 1 integer column and 1 nvarchar(255) column. Please do not ask me why, this is 3rd party vendor database. Anyway, according to my math, in the worst case scenario we should be well within 8060 limitation:
24×299 + 4 + 510 = 7690
Insert from external source fails with the above error during import process using vendor's import tool. Am I missing something in my calculation? Where possibly 11842 might come from? Any ideas?
Thank you!
"Trying to "Copy Database" from SQL Server 2016 to SQL Server 2012 through SSMS."
You cannot copy a database from a 'higher' version (2016) of Sql Server to a 'lower' version of Sql Server (2012). That's called 'downgrading' and it not supported. There are numerous Internet posts on 'options' (like scripting individual objects, etc.) for doing this type of activity.
UPDATE 2/11/2017
There is an open connect item that appears to be addressing the same problem you're having
How many actions are allowed? Does it vary by event?
I did some research and yes, there is a limit to the number of actions and events that can be added to an extended event definition. It's not a "hard" value but based on many different inputs, thus one definition that doesn't work could work with just the removal of a single event or a single action in a single event.
And it seems to vary by event, as I was able to get 30 for
sqlserver.sql_batch_completed.
You've already stumbled upon the myriad of configurations that are possible, so you know it's not entirely based on the number of actions. It's also not specific to each event, but a combination of the values.
The first item is that variable length data is the largest issue you're going to face. How do you know what's variable length and what isn't? If you look in the XE catalog sys.dm_xe_objects specifically at some actions, you'll see there is type_name and type_size columns which can be useful to see if you're adding a bunch of variable sized data points (size of 0 in the screenshot below).
Now, you're probably thinking - ok that's great but I don't know the magical limit so it really isn't helpful. Well, it is and it isn't. If you specifically look at it from a numbers point of view then yes it isn't very helpful... however this is a terrible way of looking at it. It should be looked at as, "Am I collecting only the data I need?" and in most cases you'll never run into an issue with this error.
If we take the definition in the question that doesn't work, some of the information collected seems as though it really isn't needed. Do you really need callstack, current thread id, cpu cycle time, worker address, and scheduler address? Callstack is variable, the rest are fixed, so just eliminating the callstack you could fit in more columns if needed. I'm not saying you need any more but you could.
The whole point is to limit the definition to be as small as needed. Collecting everything is going to either result in errors (as you've had here), system slowness, too much data for analyzing, or even system halting. Just because you can doesn't mean you should. There is nothing stating that these limits will or won't change between major or minor versions, so keeping the true minimum need is the best prevention. Please don't just check every box (gui) or add every action possible.
Best Answer
This is down to the implementation of how
VARCHAR(MAX)andNVARCHAR(MAX)actually store their data. If the number of bytes needing to be stored per column is less than or equal to 8000 bytes then the data will be stored in-row, if it is greater than 8000 bytes then it will be stored in LOB pages. When this happens, the the row holds a pointer to the LOB page that actually contains the data.So, working backwards, the average length of data attempting to be stored stored in each column is:
(11842 - (4 + 510)) / 299 = 38 (approximate bytes / 19 characters)
As the value for each column is underneath the 8000 byte limit, it will attempt to store the data in-row and therefore exceed the limit for a page.
You can check out sys.dm_db_index_physical_stats to find out how much is actually stored in-row vs in LOB pages. Also, SQL Hints has a decent article that covers the basics.
Since you're probably stuck with this vendor database, and the data being imported, you can force off-row storage for all nvarchar(max) columns in the table with: