Add step to JOB that checks to see if Maintenance Plan X is running

Hi All,
How can I add a step to a job that will check to see if a maintenance
plan is running? If it is, don't run the rest of the job, but if it
isn't run the rest of the job.
Thanks!
KiranYou could look at the job state:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Add step to JOB that checks to see if Maintenance Plan X is running

Hi All,
How can I add a step to a job that will check to see if a maintenance
plan is running? If it is, don't run the rest of the job, but if it
isn't run the rest of the job.
Thanks!
Kiran
You could look at the job state:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Add step to JOB that checks to see if Maintenance Plan X is running

Hi All,
How can I add a step to a job that will check to see if a maintenance
plan is running? If it is, don't run the rest of the job, but if it
isn't run the rest of the job.
Thanks!
KiranYou could look at the job state:
http://www.replicationanswers.com/D...RunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Ad hoc query running crazy slow.

Hi,
I have a job that execute a stored proc. It was running fine like 3
minutes to finish but a week ago it started to take 4 hours. Its using
ad hoc quries in that stored procedure. I reindex all the tables that
it was using. I check execution plan there was no tabloe scans. I
recompile the stored proc but no use. its sql 2000 with service pack 3
installed on it. Here is the query anyone has any clue what went wrong
suddenly. Link server is working fine.
i am running it like this
exec usp_MO_ActivityTradesBPS 'CORP'
CREATE PROCEDURE dbo.usp_MO_PositionsBPS
@.Load varchar(25) = null,
@.AsOfDate datetime
AS
Set @.Load = '%'
If @.Load is null or @.Load = ''
begin
Set @.Load = '%'
end
If IsNull(@.AsOfDate,'') = ''
Set @.AsOfDate = dbo.fn_BusinessDate(GetDate()-1)
If @.Load = 'SANFRAN'
begin
Set @.Load = '%'
SELECT RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' +
T.ACCOUNTTYPECD + T.CUSIPNR [key],
RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' + T.ACCOUNTTYPECD
BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR, T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, T.SECURITYPRICEAM, T.LEDGERBALANCEAM,
T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_Positions T
WHERE T.BRANCHCD = '001'
and T.BRANCHCD + '-' + T.ACCOUNTNR not in (Select Distinct
SM.Bps_AcctNr
From MO_InvStrategyMap SM
Where SM.Bps_AcctNr is not null)
and (T.TDMTDSECFEE <> 0
or T.TDCOmsnAm <> 0
or T.TDQUANTITY <> 0
or T.MarketValAm <> 0
or T.MTDNETPL <> 0
or T.MTDPL <> 0
or T.LEDGERBAlanceAm <> 0
or T.DAILYNETPL <> 0
or T.TDDivIntAm <> 0
or T.TDGRCREDITMTDOP7 <> 0)
ORDER BY T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD, T.ADPSECURITYNR,
T.CUSIPNR, T.CURRENCYISOCD
end
Else
begin
SELECT AM.Inventory + T.CUSIPNR [Key], rtrim(T.BRANCHCD) + '-' +
rtrim(T.ACCOUNTNR) BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR,
T.CUSIPNR,
T.ADPSECURITY1DS, T.TDQUANTITY, case when T.TDQuantity <> 0
then T.MarketValAm/T.TDQuantity
else T.SecurityPriceAm end as SecurityPriceAm,
T.LEDGERBALANCEAM, T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
T.DAILYNETPL, T.CURRENCYISOCD
FROM BPS_PositionsHistorical T
JOIN MO_InvAccountMap AM
ON T.BRANCHCD + '-' + T.ACCOUNTNR = AM.AccountNr
JOIN (Select Distinct TraderCd
From MO_InvStrategyMap
Where PrimaryRecFl = 1
and (Desk like @.Load
or Category like @.Load
or SubCategory like @.Load
or TraderName like @.Load
or TraderCd like @.Load)) SM
ON SM.TraderCd = AM.Inventory
WHERE T.BusinessDate = @.AsOfDate
and AM.DataSource like 'BPS%'
and (T.TDMTDSECFEE <> 0
or T.TDCOmsnAm <> 0
or T.TDQUANTITY <> 0
or T.MarketValAm <> 0
or T.MTDNETPL <> 0
or T.MTDPL <> 0
or T.LEDGERBAlanceAm <> 0
or T.DAILYNETPL <> 0
or T.TDDivIntAm <> 0
or T.TDGRCREDITMTDOP7 <> 0)
ORDER BY AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
end
GO
Assuming this is consistently reproduced, a few things you might want to
investigate:
- Do you have the original (3-minute) query plan? If so, what's different
from the current one?
- Looking at the query plan, where are you spending most of your time?
- During execution, are you seeing any resource constraints? E.g. > 5 avg
disk queue length, high avg disk sec/read,
- Are you seeing waits on cxpackets when you run dbcc sqlperf (waitstats)?
Might indicate parallellized queries waiting.
Suggest you dig into the query plan to see where it's spending most of its
time and check to ensure you don't have resource bottlenecks.
joe.
"DARR" <umariqbaldar@.gmail.com> wrote in message
news:1168992585.559292.8530@.s34g2000cwa.googlegrou ps.com...
> Hi,
> I have a job that execute a stored proc. It was running fine like 3
> minutes to finish but a week ago it started to take 4 hours. Its using
> ad hoc quries in that stored procedure. I reindex all the tables that
> it was using. I check execution plan there was no tabloe scans. I
> recompile the stored proc but no use. its sql 2000 with service pack 3
> installed on it. Here is the query anyone has any clue what went wrong
> suddenly. Link server is working fine.
> i am running it like this
> exec usp_MO_ActivityTradesBPS 'CORP'
>
> CREATE PROCEDURE dbo.usp_MO_PositionsBPS
>
> @.Load varchar(25) = null,
> @.AsOfDate datetime
> AS
> Set @.Load = '%'
> If @.Load is null or @.Load = ''
> begin
> Set @.Load = '%'
> end
> If IsNull(@.AsOfDate,'') = ''
> Set @.AsOfDate = dbo.fn_BusinessDate(GetDate()-1)
> If @.Load = 'SANFRAN'
> begin
> Set @.Load = '%'
> SELECT RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' +
> T.ACCOUNTTYPECD + T.CUSIPNR [key],
> RTRIM(T.BRANCHCD) + '-' + RTRIM(T.ACCOUNTNR) + '-' + T.ACCOUNTTYPECD
> BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR, T.CUSIPNR,
> T.ADPSECURITY1DS, T.TDQUANTITY, T.SECURITYPRICEAM, T.LEDGERBALANCEAM,
> T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
> T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
> T.DAILYNETPL, T.CURRENCYISOCD
> FROM BPS_Positions T
> WHERE T.BRANCHCD = '001'
> and T.BRANCHCD + '-' + T.ACCOUNTNR not in (Select Distinct
> SM.Bps_AcctNr
> From MO_InvStrategyMap SM
> Where SM.Bps_AcctNr is not null)
> and (T.TDMTDSECFEE <> 0
> or T.TDCOmsnAm <> 0
> or T.TDQUANTITY <> 0
> or T.MarketValAm <> 0
> or T.MTDNETPL <> 0
> or T.MTDPL <> 0
> or T.LEDGERBAlanceAm <> 0
> or T.DAILYNETPL <> 0
> or T.TDDivIntAm <> 0
> or T.TDGRCREDITMTDOP7 <> 0)
> ORDER BY T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD, T.ADPSECURITYNR,
> T.CUSIPNR, T.CURRENCYISOCD
> end
> Else
> begin
> SELECT AM.Inventory + T.CUSIPNR [Key], rtrim(T.BRANCHCD) + '-' +
> rtrim(T.ACCOUNTNR) BRANCHCD, T.TICKERSYMBOL, T.ADPSECURITYNR,
> T.CUSIPNR,
> T.ADPSECURITY1DS, T.TDQUANTITY, case when T.TDQuantity <> 0
> then T.MarketValAm/T.TDQuantity
> else T.SecurityPriceAm end as SecurityPriceAm,
> T.LEDGERBALANCEAM, T.TDDIVINTAM, T.TDGRCREDITMTDOP7,
> T.TDMTDSECFEE, T.MTDREALPLTDOP7, T.TDCUMPL, T.MTDPL, T.MTDNETPL,
> T.DAILYNETPL, T.CURRENCYISOCD
> FROM BPS_PositionsHistorical T
> JOIN MO_InvAccountMap AM
> ON T.BRANCHCD + '-' + T.ACCOUNTNR = AM.AccountNr
> JOIN (Select Distinct TraderCd
> From MO_InvStrategyMap
> Where PrimaryRecFl = 1
> and (Desk like @.Load
> or Category like @.Load
> or SubCategory like @.Load
> or TraderName like @.Load
> or TraderCd like @.Load)) SM
> ON SM.TraderCd = AM.Inventory
> WHERE T.BusinessDate = @.AsOfDate
> and AM.DataSource like 'BPS%'
> and (T.TDMTDSECFEE <> 0
> or T.TDCOmsnAm <> 0
> or T.TDQUANTITY <> 0
> or T.MarketValAm <> 0
> or T.MTDNETPL <> 0
> or T.MTDPL <> 0
> or T.LEDGERBAlanceAm <> 0
> or T.DAILYNETPL <> 0
> or T.TDDivIntAm <> 0
> or T.TDGRCREDITMTDOP7 <> 0)
> ORDER BY AM.Inventory, T.BRANCHCD, T.ACCOUNTNR, T.ACCOUNTTYPECD,
> T.ADPSECURITYNR, T.CUSIPNR, T.CURRENCYISOCD
> end
> GO
>

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
Jerry
Hi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to a
n
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:

> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to
an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry

Activity log backup

My company requires a 6 month retention of the archive activity logs for
SQL2000 servers. I need a job that will run each night moving the logs to an
archive file naming the logs to date.logs leaving only the "Current-date
time" log. I have set up the sp-cycle-errorlog to run each night. This
creates a new log in the number sequence.
Any assistance is greatly appreciated. Thanks.
--
JerryHi
You may want to check out http://www.sqldts.com/default.aspx?292
John
"Jerry" wrote:
> My company requires a 6 month retention of the archive activity logs for
> SQL2000 servers. I need a job that will run each night moving the logs to an
> archive file naming the logs to date.logs leaving only the "Current-date
> time" log. I have set up the sp-cycle-errorlog to run each night. This
> creates a new log in the number sequence.
> Any assistance is greatly appreciated. Thanks.
> --
> Jerry