SQL Server Job Rerun Utility

Ever had a server with numerous scheduled tasks that suffers numerous failures? It happens, one day your SQL Server is working then the next several jobs fail, you diligently rerun them all which takes you hours. Perhaps you even find the same problem again, maybe a drive is on the way out causing an intermittent problem, maybe some evil techie turned off that tape drive for purposes known only to him. Maybe your jobs are running but never finishing so you have to cancel some to free up resources.

You've got a problem. You need to fix it.

But you also need a way to quickly and easily rerun all of your outstanding jobs because that can take hours. Microsoft provide

[msdb].[dbo].[sp_start_job] 'job name'

to help out but that's only good for one job.

I've written the following code to rerun any jobs that have failed or been cancelled which fit the given name using the standard like syntax, waiting for a specified delay between each.

Syntax is

usp_ReRunJobs 'job name', 'delay'

where 'job name' is literally the textual name of the job ie if all of your full backups are called 'Full Backup Of xxxx' then 'Full Backup %', alternatively, just '%' to do everything, whilst 'delay' is in the format 'hh:mm:ss' ie '00:00:30' means a 30 second delay between each job.

You might have even tried the retries feature of scheduled jobs - that works wonders except where a job is genuinely broken and the retry impacts on the next job and so on, so usp_ReRunJobs could be scheduled to run daily after the usual job complete as a backstop.

Here's the code  :

IF OBJECT_ID ( 'usp_ReRunJobs', 'P' ) IS NOT NULL

    DROP PROCEDURE usp_ReRunJobs

go

create procedure usp_ReRunJobs (

      @Name       varchar(255)      = 'Full Backup%',

      @Delay            varchar(8)        = '00:00:60'

)

as

begin

      set nocount on

      IF ISDATE('2000-01-01 ' + @Delay + '.000') = 0

    BEGIN

        SELECT 'Invalid time ' + @Delay + ',hh:mm:ss, submitted.';

    END else begin

            create table #xp_results(

            job_id uniqueidentifier not null,

            last_run_date int not null,

            last_run_time int not null,

            next_run_date int not null,

            next_run_time int not null,

            next_run_schedule_id int not null,

            requested_to_run int not null, -- bool

            request_source int not null,

            request_source_id sysname collate database_default null,

            running int not null, -- bool

            current_step int not null,

            current_retry_attempt int not null,

            job_state int not null )

     

            declare @JName                varchar(255)

            declare cJobs cursor fast_forward for

                  select      j.name

                  from msdb..sysjobs j

                  join  msdb..sysjobhistory j2 on j.job_id = j2.job_id

                  where j.name like @Name

                  and         j.enabled = 1

                  and         j2.instance_id = (

                                                            SELECT            max(h.instance_id)

                                                            FROM        [msdb].[dbo].[sysjobhistory] h

                                                            where       j2.job_id = h.job_id

                              )

                  and         not exists (

                                                      select      x.job_id

                                                      from  #xp_results x

                                                      where x.job_id = j.job_id

                                                      and         x.running = 1

                              )

                  and         j2.run_status not in ('1','4')

                  order by j.name

            insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner= ''

            open cJobs

            fetch next from cJobs into @JName

            while @@fetch_status = 0

            begin

                  select 'Started '+@JName

                  exec msdb..sp_start_job @Job_Name = @JName

                  waitfor delay @Delay

                  fetch next from cJobs into @JName

            end

            close cJobs

            deallocate cJobs

            drop table #xp_results

      end

end

go

I'm not claiming it's the prettiest code or the most efficient but it gets the job done. The only thing I haven't managed to crack is getting the job start message appearing during the execution of the code so if you want to watch your jobs you'll have to resort to activity monitor, I have tried raiserror nowait but that didn't work either but that is a downside I can live with given on the second day of having this code in place it saved me about an hour of rerunning tasks by hand.

Roses are red, or not

Photo taken in macro mode on a Panasonic DMC-TZ5, hand held - this was the only one I took that wasn't distinctly out of focus, hand held clearly being a bad plan for macro at the best of times but I had to take the opportunity as I knew I would never get around to getting the tripod out.

GPEDIT.MSC & backing up on shutdown.

A handy tip for making backups - I have discovered another useful feature in gpedit.msc on Windows 7,  I presume it probably exists in XP & Vista as well but I cannot confirm having converted wholesale to Windows 7 lately.

If you run gpedit.msc and navigate to Computer Configuration >> Windows Settings >> Scripts you will find a start and shutdown option. Right click on either in the right hand pane and select properties & you will find a list of scripts to run at startup / shutdown.

I've created a short robocopy script to backup the user directories to a different destination on shutdown which gives me a little more security for my important files. Using robocopy makes the process pretty quick as I'm only copying over changes.

Here's a sample of the robocopy script :

robocopy "c:\Users" "d:\UserBackup" /log:"d:\UserBackup.txt" /np /xj /lev:10 /S /XF ntuser*.* *.tmp desktop.ini thumbs.db picasa.ini /xd "c:\Users\each user\AppData"  /ZB /R:1 /W:5 /purge

You will note that I exclude the AppData folder for each user - if my machine dies and I have to rebuild I'm only interested in user generated documents not any config details but essentially the exclude directory (/xd) and exclude file (/xf) section can be preety much as long as you like - and mine is in the live version.

Robocopy & UAC

One of the most annoying things about Vista & Windows 7 is User Access Control (UAC), all those pop up messages trying to stop me doing what I want to do whilst letting naughty software continue as it pleases. I figure it is worth leaving enabled just in case it helps one day.

However, that leaves me with one major annoyance and that is my ability to just run robocopy.

Well, I've found a fix it seems - I've been looking for a while and there seems to be no single answer that actually works but now I've got something. It's cheating a bit because it's working on the assumption that the user you are running as is an Administrator (you are doing backups so seems reasonable)

Create your robocopy batch file and then create a shortcut to the batch file and goto the properties of the shortcut, select "Advanced" and tick "Run As Administrator".

Now go into gpedit.msc and browse to Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> Security Options, you will find at the end of the list of settings in the right hand pane a number of UAC settings, mine are now :

You'll need to reboot after making any changes to the group policy.

Cats

Following the loss of Cylde before Christmas we adopted two big black brothers (oh er) from the Cat's Protection League.

From 2010

They've been with us for 3 weeks now and are fitting in nicely.

They came named Bertie & Bassett and are the better part of 12 years old but very friendly and like a lap to sit on in the evening.

Like all cats they seem to grab opportunities to drink out of the toilet bowl and Bassett likes blackcurrant squash - I caught him with his head in my pint glass slurping away.

Remote access to SSIS or SSRS

I've recently encountered an issue with enabling users to remotely access SSRS and SSIS (SQL Server Reporting Services and SQL Server Integration Services).

My problem comes about when the users affected are not in the Local Admins group on the destination server - we want to lockdown rights to the box whilst still allowing SQL services to be accessible via SSMS (SQL Server Management Studio).

The users can still access SSRS remotely because they can go via the web interface but as anyone who uses it frequently will know it is so slow compared to using SSMS.

The fix to enable remote access to SSIS is as follows :
Goto Control Panel -> Administrative Tools on the destination server
1) Add the users (their login accounts, or even better an AD group) to the Distributed Com Users group unders Users & Groups. We found this step necessary but that might vary on different servers, the ordering of these steps doesn't really matter so if you fancy you could try it later. We have also added the users into the other SQL server groups there too (probably just "for good measure").

2) Goto Component services -> Computers -> My Computer, right click and go into Properties, Com Security and add the same users / group with the Remote Access privilege - you shouldn't need to add others because the everyone group should do that for you.

At this point Integration Services should now be accessible via Management Studio but Reporting Services is probably telling you it does not know the machine name you've entered (try it, go on). So to finish off for Report Services :

3) Open Computer Management, browse to Services and Applications -> WMI Control and right click on it then open Properties -> Security and finally add the requried users or group with the Remote Enable privilege. This is rather brute force, if you want more granular control let me know if you find which individual modules need amending.
Job done.

HTPC Update

Well the HTPC is running nicely & the album view for Music selection is great - even Alex (4 years old) understands the interface. Still having a few problems using the texting style input (the key with * on it only choose * not the other characters).

I rather like the feature of being able to shuffle all my music (around 20,000 tracks) and play through photos at the same time. Isn't remarkable how many photos I took sideways?

One flaw has been spotted with MCE though and that is that the scheduled time does not automatically adjust when the programme moves - I guess that it's just a simple entry "record at this time & use this text to describe it" as opposed to our Freeview PVR box (Humax 9200) which seems to use the programme name (and then records every repeat on every channel which is annoying but at least not as disasterous as missing the programme entirely).