I’ve always found fork bombs funny because of their elegant simplicity, so I figured, why not build one in SQL Server?
In order to do it, I needed a way to spawn a self-replicating asynchronous process, so I built:
A stored procedure
That creates an Agent job
That runs the stored procedure
I didn’t think it was possible. I certainly didn’t think it would take a half-dozen lines of code.
Fortunately, I had set up a SQL Agent Alert for errors with Severity Level 17, which emailed me and several coworkers to alert us to the problem. But this was unfortunate too. Every one of those alert occurrences sent an email. Sure, it’s nice to know when there’s a problem, but a thousand or more emails is most certainly overkill. After addressing the transaction log issue, the alert emails kept coming. This query told me there were still a few thousand unsent emails:
Getting a message that something is down is important. Getting several thousand messages is counterproductive.
The other day I was asked How to notify multiple operators using database mail for failed jobs and a different operators for successes.
First I looked at the operator email addresses field firstname.lastname@example.org;email@example.com….etc is not helping as there is a limit on the characters in the email name entry of operator and we still have the problem we need different groups for success and failures.
It makes perfect sense, but is non-trivial. I like it.
There are three tokens within the T-SQL (highlighted in yellow above): A-MSG, DATE, and TIME. SQL server replaces these three tokens as follows:
- A-MSG: Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.
- DATE: Current date (in YYYYMMDD format).
- TIME: Current time (in HHMMSS format).
See the MSDN documentation for a list of tokens and their descriptions.
This is a great way of being smarter with alerts. Your SQL Server instance has a lot of information at the ready, so get familiar with what’s up for offer.
There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.
In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.
Kenneth’s getting fancy with animated GIFs, and gives us a good walkthrough of this aspect of SQL Agent security.
SQL Server has a number of system stored procedures that you can use to perform tasks that you might be doing in the user interface, for example… If you want to stop a job you can open SQL Server Management Studio, navigate to the job, right click and stop the job. Here is where the system supplied stored procedure comes into play. What if your busy time of the day is at 6 AM, and you want to make sure that the indexing has finished by 5:00 AM so that the system is ready to take on the day. Do you really want to wake up at 5:00 AM just to right click and stop job, in the chance that it is running?
The answer to Chris’s question is no, I’d much rather not wake up at 5 AM to stop a job if it’s running. This is why we have computers, to do that sort of thing for us.
So….when did “DatabaseIntegrityCheck – SYSTEM_DATABASES” start? At 1500 – is that 3pm? You may be able hash out that this translates to 12:15am local time…but what if you want to perform datetime-style math on the RunDate/RunTime? Sure you can do multiple leaps to say (RunDate>X and RunDate<=Y) AND (RunTime>A and RunTime<=B), but you then need to explicitly format your X, Y, A, and B in the appropriate integer-style format. Wouldn’t it be easier to just be able to datetime math?
The next part is even worse – quick – how long did the first instance of “ServerA_RESTORE_FROM_PROD_V2” run?
4,131 somethings (seconds, ms, etc), right?
Maybe (maybe!) there was a valid reason for the SQL Agent tables to have such screwy values for date, time, and duration; regardless, this is a sheer pain to deal with today.
I’ve seen the platform grow and expand quite a bit. I’ve spoken on a number of topics over the years, as my jobs have changed and my emphasis has wandered. Of all the features available, however, if I have to choose one, it would be…
I’m a programmer at heart. I grew up admiring the power of computers to execute code over and over again. I appreciate the ability of computers to remember things and remind me, or to handle them on their own.
SQL Agent has quirks, but a fully-featured scheduling system integrated into the database engine is extremely powerful.
So yeah, now it takes fewer keystrokes to get the job name. I used to:
WHERE job_id IN ( SELECT job_id
WHERE name LIKE ‘CollectorDBFilePropertiesGet-%’ );
But now I:
WHERE dbo.JobName(job_id) LIKE ‘CollectorDBFilePropertiesGet-%’ ;
Jen’s got the function available on her site.
At 1am the time jumps straight to 2am. Got any jobs scheduled to run at 01:30? They ain’t going to happen. I hope they weren’t important.
He also has the start of a script which helps fix timing issues, either from losing an hour in spring or gaining an hour in fall. This is a timely reminder (no pun intended) that Daylight Savings Time begins on Sunday, March 13th this year in the US and March 27th in most of Europe.