[Video] Office Hours: 25 Answers in 10 Minutes
It's time for a speed round, a rapid-fire tour of your top-voted questions from https://pollgab.com/room/brento:
https://youtu.be/NzO-MN8VJiE
SQL Agent, Alerts, and Operational Automation
SQL Agent, Database Mail, jobs, alerts, and operational automation.
16 associated posts16 primary posts
We’re Starting to Automate Testing for the First Responder Kit.
Brent says: It's really funny how the world works. I wrote what you see below in December 2023 and scheduled it for publication on January 16th (today), hoping to get help from the community to get this process started. On January 9, before the post went live, David Wiseman submitted a pull request with the first steps! More on that at the end of the post. I'm leaving the post as-written in Dec 2023 because it's still useful.
Get Alerted When Your SQL Server Restarts with sp_SendStartupEmail
When your SQL Server restarts, how do you find out?
I've seen a few senior DBAs do a really smart trick: they write a stored procedure to send them an email, and then they mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. (You could also try this with an Agent job scheduled to run at Agent startup, but ... Agent may not actually start.)
Ola Hallengren’s Scripts Keep Getting Better
You Know Him, You Love Him
You trust your database backups and integrity checks to him, and he has been KILLING IT on new features lately.
A while back, Ola decided to Open Source his scripts on GitHub (Soon to be known as Microsoft LiveHub for Business Essentials). As more and more people embrace GitHub and are getting used to how it works, the feature suggestions have been rolling in.
[Video] Office Hours 2018/6/20 (With Transcriptions)
This week, Brent, Erik, and Richie discuss using foreign keys in a data warehouse, SQL Server virtualization on Hyper-V clusters, having all of your SQL Server instances under the same active directory account, updating stats, changing job ownership, why you would want to go to SQL Azure, and why they have an issue with replication.
T-SQL & Development
Automating Complacency
There's no Latin word for Robot
I'm all for automation, especially when it comes to the boring stuff. As awesome as the automation routines we have for that are, I still see people messing it up.
Right now, there's no Clippy/HAL mashup glaring at you, telling you that what you're doing is a bad idea.
Production DBA
Chaining Agent Tasks For Alerting The Right Way
Step By Step
When you think about setting up maintenance, a persistent fear is that tasks may step on each other. You probably don't want backups and CHECKDB and statistics maintenance running at the same time as statistics maintenance (notice I didn't say index maintenance, here, because you're smart and you skip it?). You may also throw in some other steps, like cleanup tasks something.
SQL Server 2017: Less xp_cmdshell?
Pokin'
SQL Server 2017 RC1 dropping recently reminded me of a couple things I wanted to blog about finding in there. One that I thought was rather interesting is a new iTVF called dm_os_enumerate_filesystem. It looks like a partial replacement for xp_cmdshell in that, well, you can do the equivalent of running a dir command, with some filtering.
Simulating Workload With ostress And Agent Jobs
This question comes up a lot
Especially during Office Hours, and the answer is usually... not great. You can spend a lot of money on people and complicated software to design, run, and monitor workloads against test environments, or you can throw together tests with some free tools like SQL Query Stress or Microsoft's RML Utilities.
News & Opinion
Bad Idea Jeans Week: Building a Fork Bomb in SQL Server
Somewhat different than a sex bomb, a fork bomb is a denial-of-service attack that just starts a process that replicates itself, thereby starting more and more processes until the service goes down. Wikipedia's fork bomb page lists examples on most operating systems (including Windows).
I've always found fork bombs funny because of their elegant simplicity, so I figured, why not build one in SQL Server?
SQL Server Agent is an application server.
SQL Server Agent is a job scheduler.
When we first get started managing SQL Server, we usually use it for backups, index maintenance, DBCC CHECKDB, and other common maintenance tasks.
Forgotten Maintenance – Cycling the SQL Server Error Log
Most of us get caught up in fragmentation, finding the slowest queries, and looking at new features. We forget the little things that make managing a SQL Server easier - like cylcing the SQL Server error logs. What's the Error Log? The SQL Server error log is a file that is full of messages generated…
Not Everything Should be Automated
Some people hate repetition. They want to avoid manual tasks at all costs.
I used to be part of the cult of automation. I thought that everything should be made efficient. Why should I do something twice, if I can figure out how to do it once and then just run a script or push a button?
SQL Server Tasks You Probably Shouldn’t Automate
Every now and then I run across an automated script that does something a little suspicious. I'm not saying you should never put these things into a script, but if you do, seriously think about the surprise gotchas when someone runs the script:
DBCC commands other than CHECKDB
sp_configure (and especially RECONFIGURE afterwards)
ALTER SERVER CONFIGURATION
ALTER DATABASE
ALTER AVAILABILITY GROUP
CREATE INDEX or DROP INDEX
KILL
SHUTDOWN
And most of the database engine management stored procedures
Email Query Results Using a SQL Server Agent Job
SQL Server Agent is one of my favorite tools - it allows you to automatically schedule jobs, alert you if things are going badly, and capture information into database tables. One common request is to execute a query and send the results via email on a regular basis. This task is a great match for SQL Server…
Production DBA
Maintenance Plans and Roombas Suck – in a Good Way
Every now and then, I hear an experienced DBA say, "SQL Server maintenance plans for backups, indexes, and statistics? What, are you kidding me? Nobody uses maintenance plans because they suck."
They say maintenance plans are so inflexible because they insist on defragging or rebuilding every index, every time, regardless of how little the table has changed. Or maybe they complain about problems with scheduling, or how they saw somebody use a DBCC SHRINKDB maintenance plan once.
