What your practice for notification for another process?
-
I have a nightly production job A that has 9 steps for processing on different dbs, and after step #3, I want to add a backup dbX on a separate process (because dbX is ready for backup around midnight, and it takes 1/2 hour to back it up, it is big.), so that my main job A will not be delayed due to the back up and can continue the rest of the steps.
My question is what are my options to make this happen on SQLServer 2000? Currently, I have the backup step at the end of the job which is close to 6 am, because I need to get most of the critical step finish ASAP before 7 am. But that put me one day behind on using the dbX BAK file to restore to my test env (I am scheduling it to run at 1 am). If I move the backup step up and get kick off somehow after step #3 around midnight, this whole backup and restore process can happen during off hours and be done simulatanously, and my test env will have the latest production data.
I have thought of having a dummy file somewhere to flag the Step#3 is done, so that some other job can ping it. But I feel like there must be a better way or a easier way to implement this kind of process.
Since I am new in this area, I thought I should pick some brain out there and see what kind of practice people are doing on a similar process like this.
Thanks in advance for any feedback.
Sin
-
Sin,
I have a stored procedure I've been readying to upload into this site's Scripts area. Have not done "complete" testing on it yet, and also a little worried if it's use can be problematic for those not yet "very comfortable" with T-SQL, and able to understand exactly what the SP is doing.
That said, I use this SP, generically, whenever I have a situation like yours where you want to go ahead and start a different SQL process while in the middle of the 1st process, and return to the 1st process without waiting for the 2nd process to start & finish.
I hate how cut & pasting this code reduces all my [tabs] to single spaces
, but here it is. Let me know if it's works ok for you. If you execute the SP without parameters, it will "print" it's documentation.Create Procedure SyExecSQLSyncSP
@Command VarChar(3200) = NULL
AS
DECLARE @JobID UNIQUEIDENTIFIER,
@JName VarChar(128),
@JDesc VarChar(512),
@Now VarChar(30),
@sDBName VarChar(128),
@Note VarChar(8000)
IF @Command IS NULL BEGIN
Print 'Executes passed T-SQL batch syncronously by creating and then running a SQL Server Job.
EXEC SyExecSQLSyncSP [@Command = ''T-Sql script'']
Parameter:
@Command Up to 3200 character string of T-SQL code
Example Call:
EXEC SyExecSQLSyncSP ''EXEC SomeSP''
or to execute something in 5 minutes, but return control right away
EXEC SyExecSQLSyncSP ''WAITFOR DELAY ''''00:05:00'''' EXEC SomeSP''
'
RETURN
END -- End Doc Section
-- Check IF Agent is running
IF (SELECT count(*) From Master.dbo.SysProcesses where Program_Name = 'SQLAgent - Generic Refresher') = 0 BEGIN
SET @Note = 'Errors occured in Sync request. SQL Server Agent is NOT running.
Batch command request:
' + @Command
RAISERROR (@Note, 0, 1) With NoWait
RETURN
END
SET @sDBName = DB_NAME(DB_ID())
-- Create a Job to run passed SQL batch
SET @Now = (SELECT convert(VarChar,getdate()) + ':' + convert(VarChar,DATEPART(s, getdate())) + ':' + convert(VarChar,DATEPART(ms, getdate())) )
SET @JName = 'Temp Job (Sync DB:' + @sDBName + ') ' + @Now
SET @JDesc = 'Temp Job to run command syncronously on database:' + @sDBName
EXEC msdb..sp_add_job
@job_name = @JName,
@enabled = 1,
@description = @JDesc,
@delete_level = 1, -- delete job on completion
@job_id = @JobID OUTPUT
-- Add target server to job
EXEC msdb..sp_add_jobserver @job_id = @JobID, @server_name = @@SERVERNAME
-- Create step 1, Actual command
EXEC msdb..sp_add_jobstep
@job_id = @JobID,
@step_name = 'Run T-SQL syncronously',
@subsystem = 'TSQL',
@command = @Command,
@database_name = @sDBName
-- Execute the Job
EXEC msdb..sp_start_job @job_name = @JName, @error_flag = Null, @server_name = Null, @step_name = Null, @output_flag = 0
Once you understand the BITs, all the pieces come together
-
Hi ThomasH,
So pretent I have a sp that called "BackupAndRestoreMyDB_SP"
So does that mean I can add to my schedule Job to call:
EXEC SyExecSQLSyncSP ''WAITFOR DELAY ''''00:05:00'''' EXEC BackupAndRestoreMyDB_SP''
Then this job will continue on because this SyExecSQLSyncSP will return without wait for the "BackupAndRestoreMyDB_SP" to finish?
Cool. I have to tried it.
Thanks.
Sin
-
Just do
EXEC SyExecSQLSyncSP 'EXEC BackupAndRestoreMyDB_SP'
The WAIT FOR, was just to demonstrate the processing would return to the current "scope" nearly instantly, while knowing the command issued Syncronously would take at least 5 seconds.
Have fun.
Once you understand the BITs, all the pieces come together
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply