asked
Failed to create AppDomain "master.sys[runtime].3". .... Not enough storage is available to process this command. ,User getting Failed to create AppDomain "master.sys[runtime].3".
We have a user receiving this error message on a SQL SERVER 2014 Windows Server 2012 R2 with 64GB of memory - usually restarting the sql server service helps - but this is not a good solution. Msg 6517, Level 16, State 1, Line 90 Failed to create AppDomain "master.sys[runtime].3". Could not load file or assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008) They also sometimes receive: Msg 6532, Level 16, State 49, Line 113 .NET Framework execution was aborted by escalation policy because of out of memory. Any help is appreciated and if this isn't the right place for this question let me know.,We have SQL SERVER 2014 on a Windows Server 2014 R2 with 64GB Memory One user is receiving 2 error messages when he tries to run a query on a large table One of the messages is: Msg 6517, Level 16, State 1, Line 90 Failed to create AppDomain "master.sys[runtime].3". Could not load file or assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)
DIsk space on server: 1.79 TB with 1 TB free space - system disk has 558 GB with 519 Free tempdb is 8 MB and I don't think it is restricted. The table is a normal database table. Don't have a query plane - but I was looking at the indexes on that huge table & there isn't any! The PavementDataARANCollections has a primary key on CollectionID and an index on the CollectionPath I hadn't looked at the indexes - this person was suppose to be experienced - so I will add some indexes and see if that helps. I had suggested that they add a new field with the NAME,ROUTE
"One user is receiving 2 error messages when he tries to run a query on a large table". How large is the table? Is select * being run to such a table causing the memory consumption? What is the query or process that is cauing the error?
How much disk space is there? What about tempdb, how big is that / is it restricted? Is the table memory-resident/hekaton/whatever, or a normal database table?
Think my question got scrambled a little: One table has 14930375 records. They are running a .sql that reading this large table - looping thru & updating a smaller table of 4309 records. I can post the whole think but it's 147 lines - the query is: DECLARE row_cursor CURSOR FOR SELECT A.RWName , A.Route , A.Section , A.Direction , A.LaneNum , A.GPS_LAT_DEG , A.GPS_LON_DEG , A.GPS_ELEV FROM ARANData A LEFT JOIN PavementDataARANCollections P ON P.RWName = LEFT(A.RWName, 8) AND P.CollectedRoute = A.Route AND P.CollectedSection = A.Section ARANDATA table has 14930375 records PavementDataARANCollections table has 4309 records
Have you got a query plan for that? Just want to see what's going on under the hood, with indexes, and so on...
Lack of indexes on big table - yeah, that's not going to help... tempdb of 8MB? That sounds low. Have you only just restarted the server?
Based on all the comments, there's a lot going on here. First up, you need to establish indexes on these tables in support of the queries being run. Second, the work "loop" was used. If you're using a cursor to identify rows to update on a very large table, that could be causing all kinds of issues. Third, I would change your tempdb size. I suspect all these reboots back to the default size is also causing it to have grow over and over again. Get it to a decent size at the start. Not seeing the system or knowing anything about it, I can only guess, but 10gb doesn't seem too insane. It sounds like you don't have system monitoring in place to know how much memory, disk, cpu, is being used by your processes. I would also suggest getting that in place. Fifth, rebooting SQL Server over and over is not a good troubleshooting mechanism. Instead, run queries against the system to understand it's status. Kill a process if you have to. Finally, yes, geography data type does have special requirements including it's own style of indexing if you're doing that kind of processing, you should look into those indexes. The data type is [spatial][1]. At the root of it all, I suspect your tempdb is filling the drive and rebooting it is fixing things. I'm making this guess based on the lack of indexing for large tables which means hash joins probably which go into tempdb. Further, a cursor, which is stored in tempdb, against large data sets. There are probably other issues driving things to tempdb. Your combined column idea is not the way to go for most database design. There's just so much going on here. I guess the best place to start would be with the monitoring. For a lot more detail on these topics, I suggest getting a copy of [Troubleshooting SQL Server][2]. I'd also suggest getting a copy of [my book on query tuning][3]. You might also want to look at [this book on database design][4]. [1]:
https://msdn.microsoft.com/en-us/library/bb964711.aspx [2]:
http://www.sqlservercentral.com/articles/books/76296/ [3]:
http://www.amazon.com/SQL-Server-Query-Performance-Tuning/dp/1430267437 [4]:
http://www.amazon.com/Server-Relational-Database-Implementation-Experts/dp/1430236957
Thank you for your response. 1. Added Indexes on those tables and it did help some. 2. Have tried to convince the user to re-design that query - but he thinks that's the way to do what he needs. Have not had time to examine in detail what he's trying to do - but I know there is always more that one way to do anything. 3. Increased the size of tempdb to 10 GB maybe this will help - but there is 945 Free Space on this server and don't see any warning in the event viewer about the disk being out of space. Would you use the Process Monitor to do the monitoring or something else? I got a copy of the book & will read it too. We know rebotting the server not the answer just a stop gap solution at best. Not enough Storage to process the command? Maybe it's not really talking about disk Space? got this message today: Failed to create AppDomain "master.sys[runtime].3". Could not load file or assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)
If you have indexes and you're no longer getting the error, then now is the time to look at the execution plan to understand how the optimizer is choosing to resolve this query. You may have more indexes you can add. But you also likely need to re-architect the query. Process Monitor is not where I would do my monitoring from. Assuming you can't purchase a third party tool (my preference because I'm lazy), then you can set up Performance Monitor to watch server metrics. Add to that capturing information from the Dynamic Management Views and possibly query metrics from extended events. Most of that should be documented in the Accidental DBA book. I'm not familiar with this error. Searches indicate it's an OS issue:
https://support.microsoft.com/en-us/kb/106167?wa=wsignin1.0
16 People are following this question.
