Category Archives: SQL Toolbox
Late last night, I got the call that no DBA wants to get.
“Our Data Center has crashed”
It appears, both controllers in the VM Ware SAN crashed at the same time bringing every VM server down. Once the controllers were replaced, servers were brought back online. But that’s not the real point of this article, it is just the background of what happened.
Typically when I install a SQL server, I use a domain Security Group as the Sys Admin. Members of this group include myself, my monitoring service account and domain admins. Because I am a member of this group, I typically remove my individual login. This has not been an issue until last night; well really this morning.
SQL Agent Jobs
I had SQL Agent jobs failing all over the network once my servers were back up and running from the earlier crash and I couldn’t figure out why.
The job failed. The owner () of job SystemDB.Backup-Full does not have server access.
The owner()…does not have server access. Why did it not list the owner? Is there an owner?
I understand the importance of having non-domain accounts “owning” SQL Agent jobs; now more than ever. As much as I try to replace myself as job owner when I edit a job; it just sometimes slips my mind.
Every job owned by my network account was failing across the company. When researching I tried to get a listing of all jobs and their owners using the script below; but not all the jobs were listed. Why weren’t all jobs being listed?
Digging deeper, I remembered that syslogins is actually a view of sys.server_principles. Reading the entries there, my network login was not a principle which made sense because I removed my network login principle. But my login was a job owner; apparently SQL will use the current logged in user as the new owner ever if it is not a principle. I tested this by removing my login from a test server, creating a new job and there she blows!
wanted needed a quick way to change job ownership of all SQL Agent Jobs. So I developed the following script.
This script will be added to my “toolbox” for sure. I may set this up as a SQL Agent job itself randomly checking, or maybe a SSIS package using @Variables for Server Names, but for now, I will manually run this on severs that need it.
I have been working with SQL long enough that I have built my personal “toolbox” for SQL related tools that I use on a regular basis. They range from FREE to minimal cost (by that I mean mostly under $100; yeah, I know I am cheap).
You can find my tools here, Padre’s Toolbox and I hope to keep it updated as I test drive and use more and more different SQL programs. For now, I will briefly introduce my favorite tools (in no particular order). In the future, I hope to write a post on how and why I am using these tools.
Since my initial review of this software, Richard Fryar has even further enhanced his product to include Query Plan analysis.
This is from Brent Ozar Unlimited. Enough Said! You not only get their excellent diagnostic scripts, but e-books, whitepapers, and posters Oh My!
DMV Diagnostic Queries
A collection of free SQL tools, I find my self using these more and more for “spot checking” issues or problems.
Not technically a SQL Tools, but as a DBA we have to sometimes manage the OS itself. This tool is crucial for me to be able to remote into multiple servers without having to minimize RDP session to switch between servers.
My newest tool in my arsenal, is one that quite frankly scared the hell out of me at first. Just like Cache Plans in general. But I am mudding through the learning process and I am coming to rely more and more on this program. I haven’t purchased the “Pro” version yet, so I am still evaluating the usefulness of this program and see if I can justify the cost to my “powers-that-be”