Blog Archives

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!



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:

ALTER LOGIN [userlogin] WITH PASSWORD ‘original password’

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:


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

 When I run Windows Update, there are no updates available for Visual Studio. 
Has anyone seen this before?  My Citrix administrator does not know how to proceed.  I have never seen this before.  Any ideas?
I will blog the solution tomorrow!  Enjoy!
%d bloggers like this: