I blogged about this in August, but wanted to do this video as well:
Thanks for reading and/or watching!
SSMS
Where Is My SQL Server Errorlog?
If you need to find the SQL Server ErrorLog in a hurry and don’t want to spend 30 minutes drilling into every drive on the server:
“I don’t watch videos” version:
- Right-click the instance
- Choose ‘Facets’ from the shortcut menu
- Look for ‘ErrorLogPath’:
That’s it…let me know if you already knew this or not!
Thanks for reading!
SQL Server Registered Servers
Server Review Essentials for Accidental and Junior DBAs—Client Onboarding (Part 3)
The next post of this series is finally ready to fly. I apologize for the delay between posts, but between the rolling blackouts in Texas during the 2021 Snowpaclypse and a personal health issue, getting this written was a struggle. Let’s get back to it and take a look at the final steps in the client onboarding process.
(Also see Part 1 and Part 2 of this series)
Building a Maintenance Job List
Now that we’ve taken a look at getting the biographical data for your servers gathered, the next step is to take an inventory of the maintenance jobs that are already set up and running on your servers. We mainly want to see if proper backups are taking place, and whether or not common tasks such as integrity checks and index maintenance jobs exist. At this point we’re not concerned whether or not things are set up correctly, we only want to assess what is or is not happening so we can put together an effective plan for future data protection and preservation.
Gathering this information is easy, while deciding what to do with it will take some thought.
This can be somewhat tedious if you’re managing several instances of SQL Server, but here’s a quick way to find and chart the information you’re looking for.
In SSMS, open up your target instance in Object Explorer, then drill down to SQL Server Agent >> Jobs >> Job Activity Monitor.
This tutorial assumes the SQL Server Agent is running on your instance.
Double-click the Job Activity Monitor to open up a new window that should look something like this:
Now you can copy/paste this information into an Excel spreadsheet if you’d like to have a local copy of this information to work with. You’ll be limited to keyboard shortcuts to select all and copy—there’s no right-click “Copy with headers” available in this window.
You can see on my machine I have Ola Hallengren’s SQL Server Maintenance Solution installed to take care of all the basic maintenance tasks, as well as some addition useful maintenance jobs. In your situation, you may find similar results, a collection of Maintenance Plan jobs, or a different set of jobs altogether. Hopefully, you find something that’s already been set up to take care of backups, integrity checks, and index maintenance.
From here, you can see what jobs are active, when those jobs last executed successfully, and get a sense of the job’s schedule. Remember, at this stage of the onboarding process we’re more concerned with what jobs are set up rather than if they are set up properly.
Gathering this information will allow you to make informed recommendations and decisions going forward from this point. You’ll begin to be able to determine if the current job maintenance schedule meets your company’s Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO). If so, great, you’re in good shape! If not, then you’ve got some work ahead of you to get your maintenance jobs in order.
Once you have this information gathered and collated, you’ll be ready to tackle the next step in the onboarding process, which is building a backup frequency list. We’ll take a look at that issue in the next post.
If you’d like to know more about SQL Server Maintenance, check out Kevin’s Getting Started with SQL Server Maintenance course at Pluralsight.
Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.
TIL: Duplicate Registered Server Entries
@SQLDork continues to make my brain hurt with questions like these – Ed.
So i’m doing my usual set of work, and i think to myself, “What would happen if i made two registered server entries that point to the same server?”. Naturally, i asked Kevin (b|t) for his thoughts on it, and naturally, he told me to test it, so i did:
Running SELECT @@Servername against both these groups gives these results:
Interestingly enough, there’s that _2 appended to the second copy of server SQLPD07, which means somebody at Microsoft (t) thought this same thing at some point, so they included it in the SSMS code.
That’s all i (b|t) got for this post, this is just the part where i link to my twitter several times more than necessary, because reasons™
Thanks for reading,
Kevin and Liz
TSQL Tuesday: Life Hacks
My Pluralsight course for new SQL Server DBAs
T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Jess Pomfret (b|t), who has asked us to write about “Life hacks to make your day easier“.
So many hacks accumulated over 30+ years of adulting…
Non-technical:
Coffee timer. I will not own a coffee maker that doesn’t have an “Easy to change for the weekend” timer built-in. The last thing I need to try to do is set up coffee in the morning. I wake up, get cofee, get to work. Starts the whole day off on the right foot!
Technical, the first:
Since I am an independent consultant here under the Dallas DBAs name, I am frequently asked to come in and quickly find out why the server is so slow. Sometimes by existing customers, sometimes out of nowhere. Many of these times I cannot make any “permanent” or “lasting” changes, so my go-to is to run sp_whoisactive as a temporary stored procedure (code in link). Works the same, nothing left behind, no corporate policies violated.
Technical, the SQL:
Registered server queries:
In SSMS, we are all used to the Object Explorer window showing the SQL Servers we have connected to. A surprising number of people do not know about the Registered Servers window (Ctrl-Alt-G) that does not appear by default. In this you can register and group servers, and then run queries against all of them or any subset depending on how you group them.
My preference is use Prod and Non-Prod Groups for my clients with more than a few instances.
You can even export these settings so other people can easily have the same list without having to add them one by one. The most servers I have ever managed at once was 130. Every morning I ran SELECT @@SERVERNAME against the whole group just to see who wasn’t responding on any given day.
This is profile/machine specific, so its great for those that work in a corporate environment using SSMS from their laptop. Works well when set up on a jump box via RDP too. Not so much when the client has you remoting into a prod server directly and you have to keep changing your RDP target.
Thanks for reading!
