SQL Server Management Objects – An Update

So, after previous post called “Adventures with SQL Server Management Objects“, I decided to take a step back and see if I could improve my code. I wasn’t happy with the while(true) loop, as if the developer stars align in a certain way this could potentially mean an infinite loop.

This is Bad™.

Originally I had the idea of using Tasks (from the Task Parallelism Library) to monitor the status of switching from Idle to Executing and then Executing to anything else (signifying failure), and so I plugged away at my code briefly, while also coming to grips with the TPL. However, as time went on, I realised this code was getting messier and messier because a) I didn’t really know what I was doing, and b) as I got more familiar with Tasks and when/where to use them I realised I technically didn’t need them (as it being a console app, I didn’t really care about blocking the main thread). Not only that, but monitoring the status changes of the Job in this way was fraught with danger because sometimes (yay consistency) the time between the initial job.Start() and job.Refresh(), the job can execute and fail, making the status monitoring a bit flaky at best. So I threw away my changeset and started again (from my previous commit), this time without any usage of Tasks.

This is what I ended up with, edited for brevity;

    /* snip */
    var lastRunDate = job.LastRunDate;

    job.Start();
    job.Refresh();

    // The job.LastRunDate will only change once the job finishes executing, providing a way of monitoring the job
    while (job.LastRunDate == lastRunDate)
    {
        Thread.Sleep(_interval);
        job.Refresh();
    }

    if (job.LastRunOutcome == CompletionResult.Succeeded)
        WriteInfo("Hooray! Job Successful");
    else
    {
        WriteError("Oh Noes! Job failed!");
        WriteError("Job failed with message: " + job.GetLastFailedMessageFromJob());
    }
    /* snip */

By monitoring when the LastRunDate property of the Job object changed, I was able to accurately determine when the job had actually stopped executing from the current attempt. Thankfully, the LastRunDate property only gets changed when the Job finishes executing (whether it fails or succeeds).

If you want to look at the full changeset, the details are here.

Technically though, this still blocks the main thread. I realised after I finished this, that you could wrap that entire chunk of code above inside a Task, using –


Task.Factory.StartNew( () => {

/* monitoring code here */

});

Unfortunately, this doesn’t have any of the CancellationToken stuff implemented in it. And honestly, I’m not entirely sure where it would go, or if its even necessary. That’s for another post though 🙂

Thanks for reading folks, have a great night/day.

Advertisements

Tags: , ,

About AntiRich

I love gaming, across all platforms. My favourite game would be Counter Strike: Global Offensive.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: