Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Job fails on first Wednesday of every month Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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
Post #1586405
Posted Thursday, June 26, 2014 7:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1586413
Posted Thursday, June 26, 2014 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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.
Post #1586428
Posted Thursday, June 26, 2014 7:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1586438
Posted Thursday, June 26, 2014 7:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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.
Post #1586444
Posted Thursday, June 26, 2014 7:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Can you post that specific select into please? The entire statement.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1586446
Posted Thursday, June 26, 2014 8:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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)


Post #1586458
Posted Thursday, June 26, 2014 8:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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?
Post #1586459
Posted Thursday, June 26, 2014 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:55 AM
Points: 40, Visits: 389
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)
Post #1586476
Posted Thursday, June 26, 2014 9:00 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 672, Visits: 6,766
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.
Post #1586498
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse