One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Showing posts with label #SQLHelp. Show all posts
Showing posts with label #SQLHelp. Show all posts

Wednesday, May 21, 2014

Why Won't My Clustered Instance Start?

Last weekend I had a client page me in the middle of the night (because, of course, these things always happen in the middle of the night) with the complaint that their SQL Server Failover Cluster Instance (FCI) wouldn't come online on their B node after multiple attempts.  When they failed it back to the A node, the instance came up without issue.

The error they reported was this:
Error: 17750, Severity: 16, State: 0. Could not load the DLL SQLJDBC_XA.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.). 
If you don't recognize it outright, SQLJDBC_XA.dll is part of the JDBC (Java Database Connectivity) driver specifically for handling distributed transactions.

At first I went digging for references to this specific error, but didn't see much except for the fact that the Application Log on the server had roughly 2.3 Gajillion (a  technical term roughly equivalent to a Googolplex of Googolplexes) of these 17750 errors.

I tried to go to the SQL Server Error Log but of course the server wasn't changed from its default setting of six plus current, meaning there were only seven logs.  Since the client has tried multiple times to fail the instance back and forth, they had easily burned through the seven tries and therefore the original error.
PRO TIP: I strongly recommend changing the default log retention to a much larger number (30-45) and then setting up a job to cycle the error log every day by running sp_cycle_errorlog.  This works in all versions of SQL Server, makes the logs smaller and easier to open when you want to troubleshoot an issue, and often most importantly, protects you from the issue described above of multiple SQL Server service restarts aging out your log too quickly, or at least mitigates it (if you restart your instance 46 times, you will still be stuck).
Right-click on "SQL Server Logs" and select Configure.  Check the box for "Limit the number of error log files before they are recycled" and fill in the Maximum Number of Error Log files.  Many people are faked into believing that there is no limit because by default this box isn't checked, but if it isn't checked all you get is the *default* limit of six. #TheMoreYouKnow
I did the simple thing - I searched the B node for the file SQLJDBC_XA.dll to see if it was in the wrong directory or simply didn't exist - surprise surprise, it didn't exist.  The other possibility with this particular error (for a DLL file anyway) would be that it wasn't registered with Windows, but in this case it wasn't there at all.

I looked at the A node and searched for the file and there it was:


This showed me that the problem was probably related to a piece of application code installed on the A node, which I found without too much effort:


 I then looked on the B node and found the this ATG application wasn't there:



 No wonder the application DLL wasn't there!

As you can see the problem is that the application code was not installed on the B node and therefore the relevant database couldn't be started.  Failing the instance back to the A node allowed everything to run because the application code had been installed there.  The Windows Application Log also showed a particular database related to the situation (with a suspiciously similar name):
Date     5/17/2014 2:08:16 AM
Log     SQL Server
Source     spid7s
Message
Recovery is complete. This is an informational message only. No user action is required.
--
Date     5/17/2014 2:08:16 AM
Log     SQL Server
Source     spid51
Message

Starting up database 'atg'.
-- 
Date     5/17/2014 2:08:16 AM
Log     SQL Server
Source     spid63
Message
Attempting to load library 'SQLJDBC_XA.dll' into memory. This is an informational message only. No user action is required.
--
Date     5/17/2014 2:08:16 AM
Log     SQL Server
Source     spid63
Message
Error: 17750, Severity: 16, State: 0.
--
Date     5/17/2014 2:08:16 AM
Log     SQL Server
Source     spid63
Message
Could not load the DLL SQLJDBC_XA.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)
 ...which led into a near infinite cycle of Attempting to load - Count not load - Attempting to load - Could not load - etc. etc. etc.  Only by making it all the way back to the instance restart could you see the "starting up database" message.

--

Lessons learned for the client (and all of us):

  • Don't install application code on a SQL Server unless you absolutely have to.  Ever.  When you do have to do so, make sure all impacted SQL Servers (cluster nodes, mirroring hosts, etc.) have the same version of the application with all of the same options enabled.  (This last part gets people as well - the application is installed on both nodes but one is installed with a particular flag enabled which adds extra DLLs to the server which causes the same problem seen above).
  • Set the SQL Server Log to extra history retention so that your DBAs and other impacted parties have a better chance of seeing the true cause of the initial failure - much of the information is in the Windows Application Log but not all of it, and it is easier to find the SQL Server-specific items in the SQL Server log.  Also, on many servers the Application Log cycles more frequently than every thirty days (although you can increase the size of the Application Log as well, and this also can be a good idea.)
  • Most importantly - call for help before trying the same thing over and over.  When I try something once and it doesn't work, I do often try it a second time to make sure I didn't make a simple fat-finger mistake the first time.  After that, throw up the warning flag and ask for help via another DBA, or #sqlhelp on Twitter, or Google-Fu - whatever helps you get a second opinion.
Hope this helps!


Tuesday, May 13, 2014

T-SQL Tuesday #54 - Interviews and Hiring



http://borishristov.com/blog/t-sql-tuesday-54-interview-invitation/This month’s T-SQL Tuesday is hosted by Boris Hristov (blog/@BorisHristov) and his chosen topic is “Interviews and Hiring” – specifically interviewing and hiring of SQL Server Professionals.  This is an interesting topic to me since I have given dozens of interviews over the years and conversely have taken very few – probably less than ten.

As an interviewee it is always interesting to watch for the curve ball or the trick question, and it is actually a little disappointing those few times it doesn’t come. 

Most of them are pretty mundane but thrown in with actual technical questions, often to try to break the interview's rhythm:
  1. What is your SSRS experience?   
  2. What is the difference between putting filter criteria in the WHERE clause and in the JOIN clause?   
  3. Why is the sky blue?” (Blue light has a shorter wavelength and therefore is scattered by particles in the air – or maybe “Because IBM bought it” although this becomes less and less relevant as IBM becomes less and less relevant.)
Others are meant to be nerd-funny or start pseudo-religious arguments:
  1. Star Trek or Star Wars?” (Trek – DUH!) 
  2. What...is the air-speed velocity of an unladen swallow?” (African or European?) 
  3. My personal favorite “Why can’t your cross the streams?” (“It would be bad” is acceptable but the better answer is “Try to imagine all life as you know it stopping instantaneously and every molecule in your body exploding at the speed of light.”)
As an interviewer my curveball questions usually come as a follow-up to some answer given to a straight question – for example, when I was working at the University of Nebraska-Lincoln and giving interviews for our Business Computing group: 
  1. Why do you want to work for us?
  2. I have heard you have good educational opportunities and because I want the stability of working for a public institution.
  3. Have you done your research – we had large budget cuts the last three years running – do you consider that stable?
Questions like this are useful because they not only put the interviewee a little off-rhythm but they also raise a relevant talking point for further discussion.

I also like asking the funny/nerd questions (“Which of the thirteen dwarves of Thorin are you most like and why?”) because it opens the door to the interviewee that I am a humorous, (and at least moderately geeky) employee and that the workplace has that tone as well (if it didn’t have that tone then I wouldn’t be there in the first place to be interviewing you!)
http://img1.wikia.nocookie.net/__cb20110723162158/lotr/images/2/21/Thorin_and_Company.png
You may have noticed that I have brought up the concept of throwing an interviewee off-balance or off their rhythm a few times during this post – I want to reinforce that this isn’t because I am mean or a jerk, as many interviewees seem to think after hearing about an interview like this.  After a relatively successful response to an “off-balance question” I always tell the interviewee that I see an important part of any DBA’s job is the ability to respond to the unexpected, even while focusing intently on something else.  We spend too much of our careers - whether as a company DBA, a professional consultant, or a managed services DBA - shifting gears at a moment’s notice.  It never fails that when I am working on a major year-long project that has a deliverable Friday and I suddenly have to respond to a page that just came in for a disk full – and then five minutes later another page for a server down!

Another important related skill for a DBA that many interviews don't cover is determining which fire is more important – the answer to this is always “It Depends” – not just because that is the right answer to most IT questions 95% of the time, but because it really does depend.  Maybe the server down is for an internal infrastructure server at Client A that is important but can wait an hour, while the Disk Full from Client B is on their monster OLTP cluster that runs all of their online business – suddenly Disk Full sounds pretty important, doesn’t it?

I have always been a fan of the soft skills questions such as the ones described here over the hard technology questions.  While we do have to ask technology questions to know whether the interviewee understands the difference between a server and an instance, several key items that were new in SQL version XXX, or why we should almost always configure Max Server Memory, I am not a fan of the BOL-style questions – “what is the third parameter by position of sp_add_jobschedule?” or “What are the Registry Keys that you can edit to enable SQL Agent mail from the command line, and what is the extended stored procedure you use to do so?” I have always believed that as long as you understand the concepts and the processes, I can teach you the specifics of the technology.

The technology changes too fast to keep up with every little bit and byte that changes between versions – I would rather hire someone who has spent 10-15 years troubleshooting, researching, and figuring out how to make things work by the seat of their pants than the 10-15 year person who  happen to know the answers to the specific BOL questions that happen to be in the interview, but doesn't know how to do basic online research (basic = Google/Bing), doesn't read blogs or newsgroups, and doesn't know how to use Twitter to ask for #sqlhelp.  

Do you have to blog and tweet to be a successful Senior DBA?  Definitely not - but I find as I spend more time as a Senior DBA and interacting with other Senior DBAs, that more and more of them do blog and tweet (even if just for #sqlhelp) and even if they don't blog themselves they do read the top blogs.

Just because you do know the third parameter in sp_add_jobschedule – who cares?  99% of the time (or more) I can look it up if I really need it, and if I can’t look it up, what are the chances that of the billions and billions of things there are to know about SQL Server that I will know the one piece of minutia that is relevant.

(Oh - by the way – the third position of sp_add_jobschedule for SQL 2012 is @name, and the registry keys for enabling SQL Agent mail are HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\UseDatabaseMail and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\DatabaseMailProfile and they would be edited by using master.dbo.xp_instance_regwrite.)  #IHadToLookThemUp  #TheMoreYouKnow

Usually I end my posts with “Hope This Helps”, but in this case I will end with this:

https://www.flickr.com/photos/zen/2424581

Thursday, March 20, 2014

Error 701 - Insufficient System Memory - Now what?

This story begins, as so many often do, with a few key elements:
1) A client who experienced a problem on Thursday and waited until Friday evening to report it, even though the problem was "critical" enough to ask us to work on it all weekend if we couldn't solve it.
2) A co-worker who got locked out of the client machine, thereby getting me involved.
3) A relatively obscure error message that I hadn't seen before.
In this case the error message was an Application Log error 701 - "There is insufficient system memory in resource pool 'internal' to run this query":


...followed by lots of 18053/17300's:


The next thing that happened were DBCC MEMORYSTATUS dumps into the SQL Server error log:
 

The client reported an interesting problem - they were scripting stored procedures for modification (scripting out the ALTER PROCEDURE, editing it, and running it to apply changes) and they found that the process worked for a brief time, but then after a few successful runs, executing the ALTER statements started throwing the "insufficient system memory in resource pool 'internal'" messages.

Looking at the MEMORYSTATUS dumps in the ErrorLogs I had available, I saw that the only unusual values were very large "Pages Allocated" values for the MEMORYCLERK_XE.

The client had tried rebooting the server and restarting the MSSQLServer service multiple times before reporting the issue to us, and each time they found that it helped for a short time but within an hour or so the problem had returned.

This behavior (let's reboot - reboot always fixes it!) resulted in the SQL Server Error Logs having been cycled out so that I couldn't see the original log from the original failure.

--

BEGIN RANT

Every SQL Server should be set up to retain more than the default six logs - there are too many situations (like the one I am describing here) where six restarts (and therefore six historical logs) just isn't good enough.


Best case is to set the retention to 40-50 logs *and* to create a SQL Server Agent job to run sp_cycle_errorlog on a nightly basis (say at 11:59:59 or 12:00:01 every night) which generates a new log every day.  In most scenarios this will give you just over a month's error logs, and in a scenario such as this one, it will give multiple days' logs *and* allow for multiple MSSQLServer service restarts.

Even if you can't/don't want to implement the daily cycle job, you should definitely increase the retention to some larger number, such as 15 or 20 - the log files are relatively small text files, and even if your SQL service doesn't restart regularly (because you don't apply Windows patches, etc., in which case that is a different rant) the files don't get that large. (They can be unwieldy to open in the log viewer, but they aren't that large that they fill your disk.)

END RANT

--


Without the SQL Server Error Log from the time of the original failure, I went into the Windows Application and System Logs and the SQL Server Default Trace trying to find anything else of value.  Unfortunately the only other item was one that I saw in the available logs, "Failed Allocate Pages: FAIL_PAGE_ALLOCATION 1" which unfortunately didn't tell me anything else other than the failure was related to a Single Page Allocation (1) rather than a Multi-Page Allocation.

Possible scenarios I was able to rule out at this point:

1) SQL Server 2012 SP1 Memory Leak - Fixed by 2012SP1CU2, and they were already at 2012SP1CU8
2) Service Broker Misconfiguration - Service Broker was not enabled.
Having exhausted my Google-Fu, I turned to my next avenue - #sqlhelp on Twitter:


--


Note my rookie mistake in the middle (I blame it on working on the problem for a couple of hours on a Friday night when I wasn't really the primary on-call, but that's just ducking the blame) - I forgot to convert the pages value from the MEMORYSTATUS dump from 8KB pages into actual bytes/KB/MB/GB.  I have done this correctly many times in the past, but in this case forgetting to make the conversion made what I already knew to be a bad situation...worse.

So I knew the instance had a memory problem - but why did this suddenly start, and what was the cause?

Trying to mitigate the issue, I recommended that the client increase the memory on their VM. (IMPORTANT NOTE - this was not in any way a VM vs. physical server issue!)  The client increased the memory from 4GB to 12GB and I set the Max Server Memory to 11000MB (too high for long-term but set there to test against the 1409032 pages shown in the Tweet above), monitored the situation for another 30 minutes, and tried to go to bed, planning to review the situation in the morning.

No Good.

Pages came in overnight and when I got online I found a tweet that provided a whole new insight on the situation:



That made me pause and consider something that I hadn't thought about - in all of my Googling of the various problems earlier in the evening, I hadn't considered *which* memory clerk (MEMORYCLERK_XE) was complaining.

I checked the Extended Events section of Management Studio, and sure enough, their was a user-defined Extended Events session named "Locks" alongside the default system health session.

I checked the XE sessions DMV and found that the Locks session was almost as large as the system health session:

SELECT name, total_buffer_size
FROM sys.dm_xe_sessions
I opened the properties of the Locks session and not being an XE person myself took the most obvious route (for me anyway) - I started comparing it to the default system health session properties to see what was different - and then it hit me.  

The system health session writes to a ring buffer (memory) and an eventfile (disk).  The ring buffer target is a  memory target and the eventfile target persists the completed buffers to disk.

The user-defined Locks session was set up to write to a ring buffer (OK - or so I thought) and a histogram (hmm) but not to an event file.  I looked up the histogram and found that it too was a memory target.  So the session was set up to write to memory and memory, and I have a memory problem...I see.

To investigate further I scripted out the two sessions and looked at the definitions of the targets.   For the system health session:
ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),
ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)
For the Locks session:
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.lock_acquired', source=N'sqlserver.query_hash'),
ADD TARGET package0.ring_buffer(SET max_events_limit=(1000000))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF)
Originally I thought the histogram was the issue, but after following up with Jonathan Kehayias (@sqlpoolboy/blog) of SQLskills - when you think of Extended Events, you should think of Jonathan - I found I was mistaken.  I thought that the MAX_MEMORY=4096KB would throttle the ring buffer to 4MB.  Instead, Jonathan explained to me that the MAX_MEMORY parameter in the WITH clause just determines the "event session memory buffer space for buffering events before they are dispatched to the targets for consumption" - the buffer *before* the ring buffer.  There is a MAX_MEMORY parameter for the target but it needs to be included is the SET clause (like the max_events_limit) to affect the size of the target.  If the SET MAX_MEMORY is set, it will cause the buffer to cycle, effectively having the events overwrite themselves, once the size is reached.  With no parameter defined, there is *no* cap on the size of the ring buffer other than the indirect limit imposed by the max_events_limit parameter.

As you can see above, the default system_health session has its ring buffer set to a max_events_limit of 5,000 events, while the user-defined "Locks" session has a max_events_limit of 1,000,000 events! (Say it with me - one Millllllion events).  The catch Jonathan described to me is that events can be of any size, so you can't directly convert a certain number of events to any particular MB/GB size.

As he so often does, Jonathan went above and beyond my basic question and found he could easily reproduce this problem (the XE session causing the 701 errors) on a SQL 2012 instance in his lab using the same XE session configuration.  As Jonathan notes in this blog post on sqlperformance.com, this is an issue especially with SQL 2012 and the way XE works in 2012.

--

The catch to all of this is that had I realized it was an XE problem right away, there might (*might*) have been enough data still present in the default trace for me to see who had "Object:Created" the "Locks" object, but by the time I realized it was an XE situation the default trace had spun past the time of the original problem when the XE session was created, so there was no way (that I know of) to find the login responsible without someone confessing at the client.

The cautionary tool here is don't use a tool in Production that you don't understand (and remember, almost every environment is Production to someone!) - I have not been able to find out what the unknown admin was trying to accomplish with this session, but had they understood how XE sessions and the ring buffers work, they never would have set a max_events_limit so high without an accompanying max_memory parameter to cap the size of the ring buffer itself.

Thanks to Denny Cherry (@mrdenny/blog), Robert Davis (@sqlsoldier/blog), @sql_handle, and especially Jonathan Kehayias for their insights and #sqlhelp - community is amazing and I am proud to be part of it.

This was several hours for me on a Friday night and a Saturday early morning - hopefully this will help someone else in a similar situation in the future!