It's been a while...and my MSDB has gotten fat!

New job, new house, new year...lots of changes within the last 12 months. What hasn't changed is my love of working with SQL Server. And my annoyance with certain things.

Latest: The MSDB on a server has grown immense. What the heck is going on? So I took a look, it turns out that the MSDB backup job history has never been cleaned up.  Due to the high volume of databases on this particular server combined with tlog backups every 15 minutes it bloated the MSDB database...like ridiculously, 15 GB.

Now there's a fairly easy fix for this, you could use sp_delete_backuphistory to help clean up the MSDB database. One thing though, I would recommend scheduling it to occur after hours because with an MSDB of this size it may take a while and could potentially cause performance issues.  And increasing the size of the drive would only be a temporary solution, the real problem is that the MSDB backup history is not being cleaned up.

Why isn't there a default cleanup job in SQL set for, say, every 45 days? We clean up Agent error log files, why not the MSDB history?

Schedule this cleanup to delete entries older than a certain (rolling) date and you've got yourself a permanent fix.

What do you mean my query is broken? Search isn't working? The Fulltext Phantom.

I have a love/hate relationship with 'problems'. I really like troubleshooting, because I learn so much. Sometimes though it can be really frustrating. Luckily (and I'm sure it was mostly luck that something tickled the back of my memory on this one) I was able to quickly resolve this issue.

Ticket:

I can search and find articles with up to 2 words but anything 3 words and beyond fails to return QA results. 

What? OK, first thing is to ask lots of questions, but yes, the data they wanted was there in all environments, and the query should have returned results, and it WAS returning results, but only in Test, not QA.

Here is the query (First the base one, then a modified one, neither worked in QA):

SELECT *

  FROM [MyDB].[this].[table]

  where value like 'diana arriving at the studio%'

******second query*******

USE [MyDB]

GO

DECLARE       @return_value int,

              @RowCount int

EXEC   @return_value = [this].[table]

              @SearchText = N'FORMSOF (INFLECTIONAL,"diana") AND FORMSOF (INFLECTIONAL,"arriving") AND FORMSOF (INFLECTIONAL,"at") AND FORMSOF (INFLECTIONAL,"the") AND FORMSOF (INFLECTIONAL,"studio")',

              @Start = 0,

              @End = 100,

              @RowCount = @RowCount OUTPUT

SELECT @RowCount as N'@RowCount'

SELECT 'Return Value' = @return_value

GO

So one of my first questions is: what's different. So I look through some of the basic configurations and data, and yep all of the data, views, access etc are there. Then I stopped to think. They're doing a search. What's one of the best ways to make searches like this faster? Using Fulltext! So I went to look at the settings for Fulltext on all of the servers (prod, QA, test, etc) and found a difference. Looks like they had Transform Noise Words OFF on the offending servers. So a quick turn on:

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'transform noise words', 1;

RECONFIGURE;

GO

And the query is returning results as it should. Kind of a sneaky setting, and if you haven't had exposure to Fulltext you might not think of it. So here's your reminder!

Changing the service account for SSIS/SSAS, service won't start, error 1069

I've decided I'm going to post answers to issues that make me go ARGH instead of just sticking them into my Evernote; especially because it's frustrating when I Google the issue and can't find an answer then have to stumble around til I can get it fixed. The one today made me walk around with a red mark on my forehead from slapping myself when I finally got it figured out. Here's the story:

Where I work we had one domain acct for SQL. This was used to run all of the services, and also was a Domain Admin. I know, real secure. So we were migrating everything to a new, non-Domain Admin account, but still it's one account to run everything.

I'm sure you're rolling your eyes by now but hey, one step at a time!

Anyways, we had to change the accounts. So I wrote up a how-to: changing it in Configuration Manager, don't ever start services from there only in Failover Cluster Manager, don't forget to check the runas account in jobs,check credentials and linked servers etc.

So everything is going smoothly...until we get to SSAS. It wouldn't start after we changed the service account! This is the error:

analysis service could not be brought online with error 1069

So I tried it with my domain account. Still a no go, and giving me a bad credentials error.

I'm thoroughly confused because I'm not seeing any more information on what's going on, just the dang thing won't start. And, as we update more servers, I'm getting pings that SSIS is having the same issue.

Looking in error log, just says bad credentials, Looking in Cluster logs, no info there either.

And then the slap!

What do these things have in common? They're SERVICES! What do services need? LOG ON AS A SERVICE right! Yep, even though they're local administrators on the machine, that account also needs to be explicitly granted the Log on as a Service right, in Local Security Policy > Local Policies > User Rights Assignment.

I deserve my red forehead, but it's fixed :)

"Attempted to read or write protected memory" error

At my current workplace we have actual desktop computers, but are issued laptops in case we have to work remotely. If we want to access any of our installed programs etc we RDP into our desktop. This works out fairly well as my connection is quick enough for me to be able to do most everyday tasks when working remotely.

Then I got to thinking...

What happens if my workplace becomes a smoking hole? What if I'm unable to RDP to my desktop? I have SQL set up with my registered servers, RDCMan set up with all of the SQL instances I may need to connect to, all of my miscellaneous text files and screenshots that I haven't gotten around to archiving on the network...basically I'd be hurting.

So I decided to knuckle down and install all of my tools onto my laptop and then use a dock to connect to my monitors when I'm in the office, a fairly straight-forward process.

Until I installed SQL 2014 and tried to import my registered servers.

BAM


"Attempted to read or write protected memory" error. SQL crashes and restarts. Repeatedly. Whenever I try to log into an instance. When I try to add a registered server. Basically when I try to do anything.

What fresh hell is this??

So I did what any other good DBA would do and Googled the error. I found all sorts of links telling me to do everything from reboot my computer to replace my disk drives to give up and go home (I liked that one best!). I tried a few fixes and got a whole lot of nowhere.

Then I decided to look at what was different between my two computers. I looked at what was recently updated and saw that on my laptop that .NET had been updated to 4.5.1, Hmm I thought...I remember seeing something about this. I started digging through my emails and found one mentioning that this update 'breaks things' (yeah, real specific) and to run this to see if it helps:

netsh winsock reset

Well I ran it and BINGO, everything is running smoothly again, I'm able to log into instances using SSMS, can add my registered servers, and everything is as peachy as it can be when you're stuck in an office on a beautiful summer day.

I'm sure there are other reasons the protected memory error comes up, but this seems to be rather obscure and I wasn't able to find a fix easily so hopefully this post can help someone else!