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!

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!

You’ve Probably Had This Request Before…

So yesterday afternoon I received a management request to provide some statistics on our SQL Servers, probably to justify our positions for the next budget year. No problem, I thought, because I know how many total servers, how many clusters, how many physical boxes and how many are virtual. I also know which ones are production, acceptance, and development and I know how much RAM, how many processors and cores, how many drives and how big they all are. I also know how many licenses we own and which ones are Enterprise versus Standard, which ones are 2008R2 and which ones are 2005.

I also have a script (thanks to Pinal Dave) to tell me pretty much everything I need to know when I run it on each of my servers.  However, that was not the information they requested.   Wait for it, wait for it.

They wanted to know how many records or rows we maintained on how many tables.  In all of my years working with SQL Server, Progress RDBMS, MS Access, and MySQL (in order of awesomeness), I do not think I have ever had anyone ask for that level of information.  The quick stat that everyone wants to know is usually how many servers and how many terabytes.  In our environment, they also want to know how many dot net applications are we supporting on those database servers. 

Much to my dismay, my frantic google session showed many people who had a script that was essentially similar to Pinal’s script (although most were not nearly as fabulous).  So I did what every DBA should do, I sent up a distress call to the #SQLHelp Twitter tag!  Lo and behold who should come to my rescue, why SirSQL ((blog|twitter), who else would you expect?  Although, I was not a damsel in distress so maybe that was a poor analogy, but hey it made me giggle, so why not?

Here is his solution, which is really quite elegant.  I am thinking of automating it on one of my servers so that I can have this handy in case they ask for the information again.  It is inevitable that they will ask for it again when you give it to them once, unless of course I spent hours writing the script myself.  Thanks again, SirSQL!

 

New Year House Cleaning

As I was driving in to the office today, I could not help but think of my wife’s cleaning efforts at home yesterday. She decided the holiday was a good time to remove the clutter from our kitchen cabinets and drawers.  That inspired me this morning to do the same and hopefully put a smile on my storage guys’ faces, at least until I add my new clusters in a couple of weeks and requests some more terabyte LUNs.

A fresh new year is a good time to go through your SQL servers and look for ancient backups that maybe got moved to a different location and escaped the cycle of retention, prehistoric MDFs and LDFs from failed migrations or temporary restorations to different servers.  I know I had MDFs and LDFs from databases that were migrated to other servers that were left on the previous server as an insurance policy during the migration.  After a month, I think it is safe to remove these and clean the clutter.

This can also be a good time to review your retention policies on your back up jobs and maintenance plans to make sure you are in compliance with your department’s requirements.  Let’s all clean house and start the year off right!  Enjoy!

%d bloggers like this: