Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Job fails on first Wednesday of every month


Job fails on first Wednesday of every month

Author
Message
peter.cox
peter.cox
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 540
Morning all

Got a job here that fails on the first Wednesday of every month.

It's a complex job (66 steps at last count) that takes roughly 7 hours a night. It starts at 1am, and should be finished by 8am.

The first step normally takes under 5 minutes and is one of the quicker steps

However, on the first Wednesday of every month, the first step never finishes; it'll still be running when I come in at 8.20 (ish...). Kill the job, start it again, and no matter what it will not get past step 1. The only solution is to restart the service (not the server!), and then restart the job. When we do this it then goes through in the usual less than 5 minutes.

According to all the monitoring I'm doing, the actual step is not doing anything; there doesn't appear to be any processing, no CPU usage, no reads, no writes etc. It just will not process it

I've tried clearing the cache, both ad-hoc and proc (the step runs a SP). Although I have to confess I've not tried running the SP in step 1, seeing if that works, and then run the job from step 2. Part of the issue is we need to get the process running as quickly as possible, and I don't generally have time to experiment.

Ops and Tech Support say there is nothing running on the server (servers - as it's a cluster) - no AV downloads or anything. The fact I only have to restart the service, rather than the server would imply it's a SQL thing. I have not tried a pre-emptive restart the night before yet; downtime is not appreciated...

Anyone got any ideas? We're coming up to the next First Wednesday, and it would be handy to have some excuses ideas in place.

thanks

pete
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
What statement does it hang on?
What does that statement do?
If you run that statement separately, what happens?
Is it waiting for a file?
What are the wait types involved?
What state is that session in?
Do you have my crystal ball?


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


peter.cox
peter.cox
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 540
sorry Gail...

It hangs on a select into, but the step itself truncates a table, then selects data and put it back into the table.

Run the statement separately, it runs fine.
It's not waiting for a file - the data is all present and correct (hence it will run as normal as soon as the service is restarted)

There are no wait types - as I say, it doesn't appear to be processing at all. It starts, then literally does nothing. There is nothing unusual under sp_who2, activitity monitor, Adam Machanic's sp_whoisactive etc

When I run it against a script using sys.dm_os_tasks joined withdm_os_waiting_tasks, it will show the state as running, but there are no wait types, no blocking sessions, no resource_description etc


I'm afraid I've lost the crystal ball.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45056 Visits: 39902
If it happens at roughly the same time and only on the first Wednesday of a month, then there's something scheduled or a human is running something at the same time or....

Since bouncing the service works, it could be a parameter sniffing problem (bouncing the service certainly clears cache) cause by someone running a month-end job or something similar. My first shot in the dark on this would be to add a forced RECOMPILE to the proc.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
peter.cox
peter.cox
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 540
funnily enough i've had both

option (OPTIMIZE FOR (@StartDate UNKNOWN, @todayinJulian UNKNOWN),MAXDOP 12)

and

option (RECOMPILE,MAXDOP 12)

on there (not at the same time, obv). It happened no matter what.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
Can you post that specific select into please? The entire statement.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


peter.cox
peter.cox
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 540
here you go; fairly anonymised




INSERT INTO dbo.tblSales2
(
duedate
,gldate
,invoicedate
,PaidStatus
,OverdueStatus
,Days
,OverdueAmount
,InvoiceDate
,DueDate
,PaiementDate
,GLDate
,PayStatus
,DocCompany
,OrgID
,OrganisationDesc1
,HyperionCode
,AddressNum
,AddressBookSearchType
,CustomerID
,JDECustDesc
,EDBCustDesc
,DocNumber
,Doctype
,PayItem
,PayItemNumber
,PayItemDocType
,GrossAmount
,openamount
,TaxableAmount
,NonTaxableAmount
,Tax
,RPCRCDCurrencyCode
,CurrencyID
,MonthXRate
,ConstantXrate
,OpenAmountUSDMonthRateValue
,OpenAmountUSDConstantRateValue
,BankAccount
,ObjectAccount
,Subsidiary
,DateOfLastSentReminder
,InvoiceNumber
,remark
,NoOfPayments
,AlphaName
,ItemNumberShort
,LastUpdatedBy
,DateTimeLastUpdated
,Deleted
)
SELECT
F.duedate
,F.gldate
,F.invoicedate
,CASE
WHEN F.openamount <> 0 THEN 'Not yet Paid'
ELSE 'Paid'
END AS PaidStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'
ELSE 'Not yet Overdue'
END
END)
END AS OverdueStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian
--Overdue
THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate
--Paid but late
THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate
-- Paid on time or earlier
THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
--'Not yet Overdue'
DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
END
END)
END AS Days
,CASE
WHEN F.openamount <> 0 THEN F.openamount
ELSE F.GrossAmount*-1
END AS OverdueAmount
,CASE WHEN F.invoicedate <> 0
THEN
DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)
+DATEADD(yy, LEFT(F.invoicedate,
CASE WHEN LEN(F.invoicedate)=5
THEN 2
ELSE 3
END
)+0,0)
END AS InvoiceDate
,CASE
WHEN F.duedate <> 0
THEN
DATEADD(dd,RIGHT(F.duedate,3)-1,0)
+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS DueDate
,CASE
WHEN F.checkitemdate <> 0
THEN
DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)
+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS PaiementDate
,CASE
WHEN F.gldate <> 0
THEN
DATEADD(dd,RIGHT(F.gldate,3)-1,0)
+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS GLDate
,F.PayStatus
,F.DocCompany
-- ,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)
,F.OrgID
,O.OrganisationDesc1
,O.HyperionCode
,F.AddressNum
,CS.[Ext/Int]
,C.CustomerID
,CS.CustomerName
,C.CustomerName
,F.DocNumber
,F.Doctype
,F.PayItem
,F.PayItemNumber
,F.PayItemDocType
,F.GrossAmount
,F.openamount
,F.TaxableAmount
,F.NonTaxableAmount
,F.Tax
,CURR.Currency--F.RPCRCDCurrencyCode
,O.CurrencyID
,E.MonthXRate
,E.ConstantXrate
,F.openamount * E.MonthXRate
,F.openamount * E.ConstantXrate
,F.BankAccount
,F.ObjectAccount
,F.Subsidiary
,F.DateOfLastSentReminder
,F.InvoiceNumber
,F.remark
,F.NoOfPayments
,F.AlphaName
,F.ItemNumberShort
,'sales2' AS LastUpdatedBy
,GETDATE() AS DateTimeLastUpdated
,0 AS Deleted
FROM
dbo.sales AS F WITH (NOLOCK)
LEFT OUTER JOIN customersource CS WITH (NOLOCK)
ON CS.ERPID = 1
AND F.AddressNum = CS.ERPCustomerCode
INNER JOIN customer C WITH (NOLOCK)
ON CS.CustomerID = C.CustomerID
INNER JOIN organisation O WITH (NOLOCK)
ON F.OrgID = O.OrgID
INNER JOIN currency CURR WITH (NOLOCK)
ON O.CurrencyID = CURR.CurrencyID
LEFT OUTER JOIN exchangerate E WITH (NOLOCK)
ON E.XrateScenarioTypeID = 1
AND E.Deleted = 0
AND E.XRateTypeID = 1
AND CURR.CurrencyID = E.XrateCurrID
AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear
AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth
WHERE
(F.gldate > @StartDate OR F.gldate = 0) AND (F.duedate > @StartDate OR F.duedate = 0)
AND F.Deleted = 0
AND O.Deleted = 0
UNION
SELECT
F.duedate
,F.gldate
,F.invoicedate
,CASE
WHEN F.openamount <> 0 THEN 'Not yet Paid'
ELSE 'Paid'
END AS PaidStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'
ELSE 'Not yet Overdue'
END
END)
END AS OverdueStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian
--Overdue
THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate
--Paid but late
THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate
-- Paid on time or earlier
THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
--'Not yet Overdue'
DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
END
END)
END AS Days
,CASE
WHEN F.openamount <> 0 THEN F.openamount
ELSE F.GrossAmount*-1
END AS OverdueAmount
,CASE WHEN F.invoicedate <> 0
THEN
DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)
+DATEADD(yy, LEFT(F.invoicedate,
CASE WHEN LEN(F.invoicedate)=5
THEN 2
ELSE 3
END
)+0,0)
END AS InvoiceDate
,CASE
WHEN F.duedate <> 0
THEN
DATEADD(dd,RIGHT(F.duedate,3)-1,0)
+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS DueDate
,CASE
WHEN F.checkitemdate <> 0
THEN
DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)
+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS PaiementDate
,CASE
WHEN F.gldate <> 0
THEN
DATEADD(dd,RIGHT(F.gldate,3)-1,0)
+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS GLDate
,F.PayStatus
,F.DocCompany
-- ,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)
,F.OrgID
,O.OrganisationDesc1
,O.HyperionCode
,F.AddressNum
,CS.[Ext/Int]
,C.CustomerID
,CS.CustomerName
,C.CustomerName
,F.DocNumber
,F.Doctype
,F.PayItem
,F.PayItemNumber
,F.PayItemDocType
,F.GrossAmount
,F.openamount
,F.TaxableAmount
,F.NonTaxableAmount
,F.Tax
,CURR.Currency--F.RPCRCDCurrencyCode
,O.CurrencyID
,E.MonthXRate
,E.ConstantXrate
,F.openamount * E.MonthXRate
,F.openamount * E.ConstantXrate
,F.BankAccount
,F.ObjectAccount
,F.Subsidiary
,F.DateOfLastSentReminder
,F.InvoiceNumber
,F.remark
,F.NoOfPayments
,F.AlphaName
,F.ItemNumberShort
,'sales2' AS LastUpdatedBy
,GETDATE() AS DateTimeLastUpdated
,0 AS Deleted
FROM
dbo.sales_historicalAS F WITH (NOLOCK)
LEFT OUTER JOIN customerSource CS WITH (NOLOCK)
ON CS.ERPID = 1
AND F.AddressNum = CS.ERPCustomerCode
INNER JOIN customer C WITH (NOLOCK)
ON CS.CustomerID = C.CustomerID
INNER JOIN organisation O WITH (NOLOCK)
-- ON ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID) = O.OrgID
ON F.OrgID = O.OrgID
INNER JOIN currency CURR WITH (NOLOCK)
ON O.CurrencyID = CURR.CurrencyID
LEFT OUTER JOIN exchangerate E WITH (NOLOCK)
ON E.XrateScenarioTypeID = 1
AND E.Deleted = 0
AND E.XRateTypeID = 1
AND CURR.CurrencyID = E.XrateCurrID
AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear
AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth
WHERE
(F.gldate <= @StartDate OR F.duedate <= @StartDate)
AND (F.openamount <> 0 AND duedate < @TodayinJulian)
AND F.Deleted = 0
AND O.Deleted = 0
UNION
SELECT
F.duedate
,F.gldate
,F.invoicedate
,CASE
WHEN F.openamount <> 0 THEN 'Not yet Paid'
ELSE 'Paid'
END AS PaidStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian THEN 'Overdue'
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate THEN 'Paid but late'
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate THEN 'Paid on time or earlier'
ELSE 'Not yet Overdue'
END
END)
END AS OverdueStatus
,CASE
WHEN openamount <> 0 AND duedate < @TodayinJulian
--Overdue
THEN DATEDIFF(DAY,@ReferenceDate,dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE (
CASE
WHEN openamount = 0 AND duedate < gldate
--Paid but late
THEN DATEDIFF(DAY, dbo.ufn_Sales_GetConventionalDateFromJulian(gldate),dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
CASE
WHEN openamount = 0 AND duedate >= gldate
-- Paid on time or earlier
THEN DATEDIFF(DAY,dbo.ufn_Sales_GetConventionalDateFromJulian(gldate) , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
ELSE
--'Not yet Overdue'
DATEDIFF(DAY,@ReferenceDate , dbo.ufn_Sales_GetConventionalDateFromJulian(duedate))
END
END)
END AS Days
,CASE
WHEN F.openamount <> 0 THEN F.openamount
ELSE F.GrossAmount*-1
END AS OverdueAmount
,CASE WHEN F.invoicedate <> 0
THEN
DATEADD(dd, RIGHT(F.invoicedate,3)-1,0)
+DATEADD(yy, LEFT(F.invoicedate,
CASE WHEN LEN(F.invoicedate)=5
THEN 2
ELSE 3
END
)+0,0)
END AS InvoiceDate
,CASE
WHEN F.duedate <> 0
THEN
DATEADD(dd,RIGHT(F.duedate,3)-1,0)
+DATEADD(yy,LEFT(F.duedate,CASE WHEN LEN(F.duedate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS DueDate
,CASE
WHEN F.checkitemdate <> 0
THEN
DATEADD(dd,RIGHT(F.checkitemdate,3)-1,0)
+DATEADD(yy,LEFT(F.checkitemdate,CASE WHEN LEN(F.checkitemdate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS PaiementDate
,CASE
WHEN F.gldate <> 0
THEN
DATEADD(dd,RIGHT(F.gldate,3)-1,0)
+DATEADD(yy,LEFT(F.gldate,CASE WHEN LEN(F.gldate)=5 THEN 2 ELSE 3 END)+0,0)
ELSE NULL
END AS GLDate
,F.PayStatus
,F.DocCompany
-- ,ISNULL(BusinessSector.ReportingSiteOrgID, F.OrgID)
,F.OrgID
,O.OrganisationDesc1
,O.HyperionCode
,F.AddressNum
,CS.[Ext/Int]
,C.CustomerID
,CS.CustomerName
,C.CustomerName
,F.DocNumber
,F.Doctype
,F.PayItem
,F.PayItemNumber
,F.PayItemDocType
,F.GrossAmount
,F.openamount
,F.TaxableAmount
,F.NonTaxableAmount
,F.Tax
,CURR.Currency--F.RPCRCDCurrencyCode
,O.CurrencyID
,E.MonthXRate
,E.ConstantXrate
,F.openamount * E.MonthXRate
,F.openamount * E.ConstantXrate
,F.BankAccount
,F.ObjectAccount
,F.Subsidiary
,F.DateOfLastSentReminder
,F.InvoiceNumber
,F.remark
,F.NoOfPayments
,F.AlphaName
,F.ItemNumberShort
,'sales2' AS LastUpdatedBy
,GETDATE() AS DateTimeLastUpdated
,0 AS Deleted
FROM
sales AS F WITH (NOLOCK)
LEFT OUTER JOIN customerSource CS WITH (NOLOCK)
ON CS.ERPID = 1
AND F.AddressNum = CS.ERPCustomerCode
INNER JOIN customer C WITH (NOLOCK)
ON CS.CustomerID = C.CustomerID

INNER JOIN organisation O WITH (NOLOCK)

ON F.OrgID = O.OrgID
INNER JOIN currency CURR WITH (NOLOCK)
ON O.CurrencyID = CURR.CurrencyID
LEFT OUTER JOIN exchangerate E WITH (NOLOCK)
ON E.XrateScenarioTypeID = 1
AND E.Deleted = 0
AND E.XRateTypeID = 1
AND CURR.CurrencyID = E.XrateCurrID
AND YEAR(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateYear
AND MONTH(dbo.ufn_Sales_GetConventionalDateFromJulian(F.duedate)) = E.XRateMonth
WHERE
(F.gldate <= @StartDate OR F.duedate <= @StartDate)
AND (F.openamount <> 0 AND duedate < @TodayinJulian)
AND F.Deleted = 0
AND O.Deleted = 0

OPTION (MAXDOP 12)
peter.cox
peter.cox
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 540
The function - as I'm sure you're going to ask, is solely

Select @Result=
Case When @JulianDateToConvert <> 0
THEN
dateadd(dd,right(@JulianDateToConvert,3)-1,0)
+dateadd(yy,left(@JulianDateToConvert,
case
when len(@JulianDateToConvert)=5
then 2
else 3
end
)+0,0)
ELSE NULL
END

I have wondered about this function, and whether that's the cause...but functions don't get cached do they?
peter.cox
peter.cox
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 540
Also,the two indexes on the table are dropped at the start of the SP, and recreated in a further step.

It gets past the drop index, and truncate easily (we have a debug system in place, so i know where it gets to in the SP)
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8257
Just out of curiosity -
Have you ever tested with a Date / Year / Month / Julian table? And maybe one with current date populated daily?
We used this, and found having a arbitrary date for 0 to be very useful.
This simplified code in many places, and performed very well.

I'd see if a trace at the start of the job shows anything.
See if there is any memory pressure, or unexpected IO happening.
I assume you are not running SSAS on the same server.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search