May 18, 2016 at 2:37 am
Hie Experts,
Please advise,I have two sql server agent jobs Job A and Job B.Can i use a procedure sp_HostProcess in both jobs which must run both at the same time.
May 18, 2016 at 2:54 am
It depends what sp_HostProcess does. For example, if it creates a permanent table (or a global temp table) then you're likely to get an error in one of the jobs that the table already exists. If it performs updates on a large table then you're going to experience blocking. So yes, you can run both jobs at the same time, but you need to be mindful of the potential effects.
John
May 18, 2016 at 3:16 am
This is what the proc does.
CREATE procedure [dbo].[sp_HostDriveStatusProcess]
(
@SiteName nvarchar(100),
@HostIP nvarchar(15),
@SystemDrive nvarchar(1),
@datadrive nvarchar(1),
@BackupDrive nvarchar(1),
@BackupDrive2 nvarchar(1)
)
as
begin
set nocount on
select
a.systemname as HostName,
left(a.deviceid,1) as DriveName,
cast(cast(a.drivesize as numeric(20,2))/1024/1024/1024 as numeric(13,2)) as TotalSpace,
cast(cast(a.freespace as numeric(20,2))/1024/1024/1024 as numeric(13,2)) as FreeSpace
into #Table
from [dbo].[DriveStatus] as a
where Active = 1 and
drivesize not like '%empty%'
and freespace not like '%empty%'
order by
a.DeviceID
truncate table [dbo].[DriveStatus]
update [xxxxxxx].[DBA].[dbo].[HostDriveStatus]
set Active = 0
where SiteName = @SiteName and HostIP = @HostIP and Active = 1
insert into [xxxxx].[DBA].[dbo].[HostDriveStatus]
(SiteName, HostName, HostIP, SystemDriveName, SystemTotalSpace, SystemFreeSpace, SystemFreeSpacePercentage,
DataDriveName, DataTotalSpace, DataFreeSpace, DataFreeSpacePercentage,
BackupDriveName, BackupTotalSpace, BackupFreeSpace, BackupFreeSpacePercentage,
Backup2DriveName, Backup2TotalSpace, Backup2FreeSpace, Backup2FreeSpacePercentage
)
select
@SiteName as SiteName,
a.SystemHostName as HostName,
@HostIP as HostIP,
a.SystemDriveName,
a.SystemTotalSpace,
a.SystemFreeSpace,
cast(a.SystemFreeSpace/a.SystemTotalSpace as numeric(13,2)) as SystemFreeSpacePercentage,
b.DataDriveName,
b.DataTotalSpace,
b.DataFreeSpace,
cast(b.DataFreeSpace/b.DataTotalSpace as numeric(13,2)) as DataFreeSpacePercentage,
c.BackupDriveName,
c.BackupTotalSpace,
c.BackupFreeSpace,
cast(c.BackupFreeSpace/c.BackupTotalSpace as numeric(13,2)) as BackupFreeSpacePercentage,
d.BackupDrive2Name,
d.Backup2TotalSpace,
d.Backup2FreeSpace,
cast(d.Backup2FreeSpace/d.Backup2TotalSpace as numeric(13,2)) as Backup2FreeSpacePercentage
from
(
select
a.HostName as SystemHostName,
a.DriveName as SystemDriveName,
a.TotalSpace as SystemTotalSpace,
a.FreeSpace as SystemFreeSpace
from #Table as a
where a.DriveName = @SystemDrive
) as a
join
(
select
a.HostName as DataHostName,
a.DriveName as DataDriveName,
a.TotalSpace as DataTotalSpace,
a.FreeSpace as DataFreeSpace
from #Table as a
where a.DriveName = @datadrive
) as b on a.SystemHostName = b.DataHostName
join
(
select
a.HostName as BackupHostName,
a.DriveName as BackupDriveName,
a.TotalSpace as BackupTotalSpace,
a.FreeSpace as BackupFreeSpace
from #Table as a
where a.DriveName = @BackupDrive
) as c on c.BackupHostName = a.SystemHostName
join
(
select
a.HostName as Backup2HostName,
a.DriveName as BackupDrive2Name,
a.TotalSpace as Backup2TotalSpace,
a.FreeSpace as Backup2FreeSpace
from #Table as a
where
a.DriveName = @BackupDrive2
) as d on d.Backup2HostName = a.SystemHostName
drop table #Table
end
GO
May 18, 2016 at 3:25 am
Yeah, that'll be a problem
truncate table [dbo].[DriveStatus]
If you have two things running that procedure at the same time, one's going to end up working on wrong data or have the data its working on truncated part way through.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 3:31 am
Thanks
So how can i come around the solution.Must i schedule jobs at different times?
May 18, 2016 at 3:35 am
That or change the procedure so that it can be run by multiple processes without problems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 3:49 am
Thanks , i will make the jobs not to run at the same time.I am not good to tsql.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply