Category Archives: Troubleshooting
Sometimes It’s Best Not To Monkey Around
Recently I found a poorly performing server and I could not figure out why with all of the usual troubleshooting techniques. Something told me to look at some of the configuration settings and there it was shining like a crazy diamond. The sp_configure option ‘max worker threads’ was set to a value of 16. Seriously?
Now my curiosity was piqued. There are experts who change settings like this because they know what they are doing. Generally, I do not monkey around with these types of settings unless I have a really good reason to do so, and no a vendor telling me to do it is not a good reason unless this SQL Server is dedicated solely to them, which it is not! Best practices says to leave this at zero so that SQL Server can manage the number of worker threads for optimal performance. Who am I to think that I know better than some really smart guys from Redmond? After doing some light reading on the subject, I learned that too few threads can cause “thread starvation” where there are not enough threads to service the incoming client requests resulting in poor performance.
In addition, setting this value too high can waste memory and also be detrimental to performance. In conclusion, do not monkey around with the settings unless you know what you are doing. That is all!
Enjoy!
Maintenance Plans All Died…See the Magic
Last night on one of my production clusters, all of the maintenance plans died. On my drive in to work, I thought it was probably a drive space issue, but it was also strange that I did not receive an alert for that. Finally at the office looking at the Job Activity Monitor then view history, I get a lot of useless information that says simply “the step failed” during my reorganize index routine. This is not much help.
When looking at the Maintenance Plan view history, then I actually see a useful error: “Alter failed for Server <servername>.” When looking up this error ,I narrowed it down to two plausible solutions. The first being to check to make sure that “Automatically set I/O affinity mask for all processors” is checked. We don’t generally change that from the default. Next.
The second option was to make sure sp_configure “allow updates” was set to zero. I thought it was, especially since this is a 2005 box and the feature is supposed to be deprecated (books online confirmed that for me, interesting though it says the functionality is unavailable). Low and behold “allow updates” was set to one. A quick reconfigure and the maintenance plans are running again.
This is a temporary fix because the new server I am migrating these to at the end of the year will be running Ola Hallengren’s backup solution, like my other new servers. Bye bye maintenance plans!
Enjoy!
My Mistake For Your Enjoyment
This morning when an aggravated user contacted me that they could not get into a new database that I setup on Friday, I discovered that I had made a mistake. Well that’s never happened before, well not today anyway.
Alright, here we go. I created three new logins that would access development and acceptance copies of a production database that I had just setup on an acceptance server using Red Gate‘s SQL Compare and SQL Data Compare (which I will blog about soon, excellent product). In my possession, actually a sharepoint list, I had the password for each of these three new logins. Today I found out that the password I had listed was out of date. No problem, easy fix!
Using SSMS I would easily change the passwords and then update my sharepoint list to the new passwords, this is DBA 101 stuff. Not so fast my friend! SSMS then presented an error stating that the password did not meet our ultra-rigid fort knox password policy (see posts on security). No problem, I forgot to uncheck the ‘Enforce password policy’ check box. That happens all the time when I get in a hurry. Now it is unchecked and SSMS presents the following error:
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON.
There was my real mistake committed on Friday; I forgot to uncheck the ‘User must change password at next login’ check box when I created the logins. It was a rookie mistake, but I was in a hurry to get things done so I could go and get my root canal in the afternoon. Luckily for me this was not a resume updating event. In order to fix this we need to do the following:
USE MASTER
GO
ALTER LOGIN [userlogin] WITH PASSWORD ‘original password’
GO
ALTER LOGIN [userlogin] WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
Then I was able to change the password, which I used ALTER LOGIN since I had it in the query window. What a way to start the week off. Enjoy!
My Attempt To Install SSMS on Citrix….Pass (Part Two)
Yesterday I told you about my failed attempt to install SSMS for SQL Server 2008 R2 as a Citrix application and the failure that ensued. My apologies for making you wait a day for the solution, but I was rushed with another production issue yesterday and decided to break this into two posts for dramatic effect.
Drumroll please!
I found the following solution on several websites including Microsoft:
You need to change the following registry key locations:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VC\Servicing\9.0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VC\Servicing\9.0\RED\1033
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\DevDiv\VS\Servicing\9.0
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\DevDiv\VS\Servicing\9.0\VSR\1033
Change the values of SP and SPIndex to 1 and SPName to SP1. The original values in my case were 0 and RTM, respectively. This is a strange issue because Visual Studio 2008 was never installed on this machine.
I hope this helps! Enjoy!
My Attempt To Install SSMS on Citrix….Fail
I had a goal in mind: install SSMS on Citrix so that if I had to look at one of my SQL servers, I would not need to remote desktop into my desktop or the server itself, in case I needed to look at multiple servers. I wanted just to set up SSMS on the Citrix server as an application so that I could quickly assess problems and avoid redecorating my three monitors when the icons get squished down into one RDP session.
I downloaded the 2008 R2 SSMS installer from Microsoft instead of using my media. After running the installer, I get the following error:
SSMS Install Error
SQL Swampland 