I learned an important lesson this weekend about updating system tables directly. I am getting ready to migrate to a new SQL Server and am setting up agent jobs to match what is on the old server. I scripted them out and then I ran them on the new server. Then I saw that many of them were enabled. Rookie mistake! I was in a hurry and googled for an Agent job disable script and found this one:
SET Enabled = 0
WHERE Enabled = 1;
“Awesome”, I thought to myself and kept going. But over the weekend our on-call started getting alerts of jobs failing from the new server. How was this possible? They all showed disabled?
Well, it turns out that SQL Agent didn’t know they were disabled because I didn’t use the proper Stored Procedure to update the jobs. SQL Agent thought it was supposed to run them, so it kept running them even though they were marked as disabled. We shut down SQL Agent until I could figure out why they were running and that is one of the fixes. By “rebooting” the agent, it sees all the jobs are disabled and updates. For Reference, here is the proper way to update jobs to disabled or enabled From HowardH on SQLServerCentral:
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs
SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs WHERE enabled <> 0
I added a where clause to the enable script so that if you run them together, you will get all the jobs you should re-enable next time.
Greetings friends! This is a feelings post (you have been warned). Tomorrow I start my new job. Those that see me on a regular basis were shocked to hear I had accepted a new position. It was kind of a shock to me too. I have loved my time with my now previous employer. I was happy, challenged and most importantly appreciated. But then I got a call, an opportunity to work for one of the companies on my list of “Dream Places to Work”. I talked to my boss, he is a coach at heart and told me that I had to at least meet the team and know what I would be turning down or accepting so that I wouldn’t look back and wonder what might have been. I will forever be grateful for the lessons he taught me and the encouragement that he so kindly provided daily.
I did what he said, I met the team, toured the facility, asked questions and finally accepted the offer. It was such a hard choice to leave, if I could work at two places at once, I would have done that. I have been asked a bunch what made me choose to take the offer, so here goes:
1. Opportunity to work with advanced systems, HA and DR that is mature and the chance to learn from it. I won’t lie, I am nervous about learning new stuff, but I also love it, so I am sure it will be good.
2. Opportunity to learn the advanced features of SQL Server that only Enterprise level systems can provide.
3. An awesome team. I have become a bit of a feral DBA, so we will see how this goes. I very much hope that they are understanding of my crazy ideas, weird habits and kind in teaching me how to work with a team again. I also am so excited to learn new things, and be a part of something awesome. I have a lot of information in my brain, but I don’t always trust it, so it is good to have people to bounce ideas off and to gut check my ideas.
4. An awesome boss, his team spoke so highly of him, I knew I would be in good hands. This was also key. I had such an amazing boss already, there is a lot for him to live up to, but seeing the way the team admired and listened to him, plus tease him, helped me see that this could work for me.
5. A chance to learn ASL and make a difference. I have always wanted to learn American Sign Language, now I have a chance. I am super nervous about signing the wrong thing or offending someone (classic Andrea) but I know the only way to learn and get better is to try. Also, I get a sign name, which was kind of the seal on the deal. I am excited to learn what it is tomorrow. Helping people is something that has always been important to me. I make the joke that I am like Mary Poppins in that I only stay as long as I am needed. But making a difference is something that I need. I need to know that I am helping people live better lives, and this is definitely something that helps people.
My princess dresses are also a way to help people. I volunteer work for children’s charities and my personality is a big part of that work. I recently had a discussion with someone about not being just a character. He asked if I should stop wearing the princess dresses because it hides who I am and makes me a character instead of a person. I thought about this question a lot for a few days. I even talked to people about it and asked if they thought that I was more of a joke doing it. But it always came back to me and what I wanted. I feel that me dressing as a princess shows how I feel on the inside but am too shy to show. It is me trying to bring joy to other people. It is a reminder that we all have something special that we can share with the world to make the world better. We can help build up each other and also make the difference in the life of a child. I give a lot of reasons why I do what I do, but at the end of the day, it is because it builds me up so I can keep building other people up. I hope you have felt that love and support and if not, send me a tweet so I can tell you what I see in you.
I spent a lot of time trying to decide what to do. I think a specific song by The Struts made me feel that I had to do it:
I wanna taste love and pain
I wanna feel pride and shame
I don’t wanna take my time
I don’t wanna waste one line
I wanna live better days
Never look back and say
It could have been me
It could have been me
So, tomorrow, it’s me and I am hoping for the best.
I am approaching my last day at my current job. I love it here and will be really sad to leave, but have an awesome opportunity to grow my knowledge and career with a company on my “want to work for” list.
There are a lot of things to take care of before I leave. I have been updating documentation (with meme’s) so that it is useful and fun. I am trying to wrap up all my tickets and outstanding items and last night I woke up and realized, I was the owner of some databases. This is how I fixed it:
I launched a query window on my Central Management Server to save time, but you can run this on one server at a time if you want. I used the syntax from sp_helpdb to find out what I wanted to query:
select name, isnull(suser_sname(sid),'~~UNKNOWN~~') AS Owner, convert(nvarchar(11), crdate),dbid, cmptlevel
WHERE suser_sname(sid) = 'domain\MyUserName'
Some of the applications in my environment run under a special user and I didn’t want to interfere with those, I just wanted to fix the ones that use me. Then I borrowed some code from Brent Ozar:
SELECT 'ALTER AUTHORIZATION ON DATABASE:: ['+ name +'] to sa;'
WHERE suser_sname(sid) = 'domain\MyUserName'
Here’s one I run on the CMS to find any SQL Agent Jobs that I own across my enterprise and then I can run the update scripts that are generated on the individual servers.
SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + [Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)+';'
WHERE SUSER_SNAME(owner_sid) = 'domain\MyUserName'
It is painful when you have been working on a tough query and you start to get an out of memory error.
“An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown”
What in the world? But it was working before! Why is this happening? Is it happening for everyone? Do I need to reboot the SQL Server?
This is a SQL Server Management Studio Error. Usually it means that you have filled up the local memory cache on your machine. Save all your queries and close Management Studio. Once you open it again you “should” be fine, but famous last words right? If that doesn’t work, just reboot. Nice clean memory and you can get back to playing with your super tough query.
Today I needed to quickly check 68 SQL Server Instances Min and Max memory settings. I didn’t have time to go through each one and I know I will need to do this again in the future. Thank goodness I have my Central Management Server configured with all those servers. I was able to connect to my main CMS server, and run this simple query that will tell me all my servers min and max memory setting:
SELECT ServerName, [Max], [Min]
(SELECT @@ServerName AS ServerName, LEFT(name,3) AS Memory, value
WHERE name like '%server memory%') AS SourceTable
FOR Memory IN ([Max], [Min])
) AS PivotTable;
Then, I also used this code from my last post but added a server name to it so I could see what memory was available on each server.
SELECT @@Servername,physical_memory_kb * 9.5367431640625E-7
I frequently am trying to determine how much RAM I can give to SQL Server without starving the OS. Since my servers usually only have SQL Server on them and no other applications, I can give them everything except 2-4gb for the OS. Depending on what that server is doing and if it still looks hungry, I will give the OS more or SQL Server more. The question is, if I can’t remote on to the server, how do I know how much RAM is on the server? After much searching and calculating, I have come up with a T-SQL query to help. It will tell you how much RAM is on your instance.
SELECT physical_memory_kb * 9.5367431640625E-7
It uses the system views which I haven’t begun to scratch the surface of their awesomeness, but plan to learn more.
TSQL Tuesday #63 – How do you manage security?
Yay, so this is my first official blog party post. I was late with my post last month because I didn’t have it figured out until I saw everyone posting and wondered what was going on.
One of my goals for this year is to increase security across my 50 SQL servers. The SA log-in is a dangerous one and I don’t want it out there on my servers, the trouble is that it is hard to exterminate. Here is my new plan.
I am going to script out my SA log-in and password using this script.
Next I am going to rename the SA log-in to something else and give it a much stronger password. This information will go into my password safe so that I have it just in case I need it.
I am going to create a dummy SA. I have many vendor databases that claim they need SA, but they really don’t and I don’t want them to have high privileges. I am going to work on lowering the privileges to only give what they need (this will be a slow server by server process to get the permissions right). The reason I am keeping a log in with the name “SA” is because of vendors who hard code that user name.
As I was talking to people in the #SLCSQL user group last night, someone suggested that we can also monitor that dummy SA login to watch for attacks. It is a great idea and plan to include monitoring on the new log in.
Lars Rasmussen suggested I have a user on each server that will always be there to handle running jobs and other database needs. I plan to include this too so that when I don’t have a proper SA log in, I will still have a log in that can handle all my fun stuff.
By doing all of this, my SA won’t bite me anymore.
T-SQL Tuesday is a blog party started by Adam Machanic (b|t) just over five years ago. This month it is hosted by Kenneth Fisher (b|t).