Bert has a great topic for this month, he wants us to write about code you’ve written that you would hate to live without.
I have found the SQL server community to be extremely helpful especially when it comes to finding good code sources, these sources have inspired me to write my own code and to also share with the community ,not sure if any of it actually gets used 😛
Here is some code I have written that helps us get by on a daily basis, I have narrowed my list down to four of my own but we do have lots more available such as @dbadavidfowler fantastic sp_restorescript with more code due to hit our Github repo.
We receive an email from the Inspector every morning for each SQL stack, its a great place to start for a quick visual of the housekeeping type tasks all in one place.
One of my favourite parts of the Inspector is the drive space usage module, it helps us keep on top of drive space usage and helps predict how long it will be until the disks are full, it also includes a last 5 days of usage which can be really handy for a glance when things have taken a sudden spike within the last few days.
Currently I am working on V1.2 for the Inspector which can be found on our Git Projects page
If you would like to know more about the Inspector please check out the following blog posts:
If you have some good ideas for things you would like to see included in the Inspector or would like to contribute code please head over to our GitHub Repo and raise an issue , we would love to hear from you 🙂
This is a great goto proc for an alternative to the Always on availability group GUI for changing Failover mode, Synchronous mode or even Readable options.
When you manage multiple servers with multiple Availability groups this stored procedure can save you alot of time, sometimes I find the GUI can take a long time to open but equally it can take some time to execute the command.
sp_AGreconfigure can speed this process up for you, we tend to use this as our goto for switching synchronous settings when patching/rebooting replicas but also I tend to use it in @Checkonly = 1 mode for giving the Availability group settings a once over.
For more details on the procedure can be found here
I decided to write sp_Runningjobs because when I need to check for running jobs across multiple servers I can simply run this stored procedure against my registered servers list which makes checking so much faster than opening up the job activity monitor on each server individually, More info can be found here
A quicker way of checking the error log for failed logins 🙂 more info can be found here
Thank you for reading 🙂