It is 2 O’Clock in the morning, trying to optimize some SQL Code.
Basically trying to see why importing 200 K records from a flat file into a staging table is taking 2 minutes.
And, another 3 minutes from the staging table into the actual table.
Ran into storage issue.
Thanks goodness that we have good alerting.
First it was a notification that we have gone below 10%, then came gone below 4%.
By the time I got to the computer, available storage is at 0.
Detached database and moved the data and log files to another drive.
Restarted the scheduled job and it quit right away with the error message pasted below.
Msg 10314, Level 16, State 11, Procedure sp_XXXX, Line 232 [Batch Start Line 2] An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 'clrFile, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString)
The problem is that once I detached the database is was no longer trusted.
When a database has CLR programmable objects the speediest thing to do is mark it trusted.
Issued ALTER DATABASE [DBPerf] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; Issued ALTER DATABASE [DBPerf] set TRUSTWORTHY on with NO_WAIT; Issued ALTER DATABASE [DBPerf] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
Dedicated to our support staff.
Work 24 hours on monitoring and triaging problems.
They forgo sleep so we don’t have to.