September 18, 2008 at 2:45 am
Hi ,
I have ETL running everyday ,sometimes it hangs on one of the pakages .if I stop it and rerun again it will not work , I copy past query in sql management studio it runs for ever too ,untill I restore back the database one day and run the job again , althought I have more records added to old ones ETL runs in expected time successfully after restore.
note: I tried uncheck no loks , create indexes , I used tuning advisor .and more tools like
DBCC CHECKALLOC
DBCC CHECKCATALOG
DBCC CHECKDB
DBCC SQLPERF
no errors reported
nothing help till now any suggestion ?
September 18, 2008 at 3:00 am
Nassim (9/18/2008)
Hi ,I have ETL running everyday ,sometimes it hangs on one of the pakages .if I stop it and rerun again it will not work , I copy past query in sql management studio it runs for ever too ,untill I restore back the database one day and run the job again , althought I have more records added to old ones ETL runs in expected time successfully after restore.
note: I tried uncheck no loks , create indexes , I used tuning advisor .and more tools like
DBCC CHECKALLOC
DBCC CHECKCATALOG
DBCC CHECKDB
DBCC SQLPERF
no errors reported
nothing help till now any suggestion ?
Hi Nassim,
What error message(s) are you getting, as not much detail to work with here? Has the package ever worked properly?
Thanks,
Phillip Cox
September 18, 2008 at 3:26 am
Thanks for your reply .
most of the time it works properly , and when it hangs no error message appears . in event viewer shows info message about last container finished processing . but processor is still running and the job is running but it will never finish , I thought maybe it's taking longer time so I waited 1 day but no progress . untill I stop the job ,restart sql and restore back database and run the job from begining it will run successfuly .
September 21, 2008 at 10:52 pm
There could be many reasons why the job hangs....
what your query is doing?
did you check
> execution plan for the query?
> is it takes dynamic parameters? like datefrom dateto?
> is data tables are getting blocked?
September 22, 2008 at 2:04 pm
Are you running a process loading a table based upon a view on the same table? If so, make sure that the bulkload option's set off.
It'd be worth using enterprise manager, sp_who2 or best of all IMHO Erland Sommarskog's aba_lockinfo to check for locks causing the problem next time it occurs
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 22, 2008 at 5:57 pm
I dont have any query problem , because it's working most of the time , I think my problem is memory management .sometime when I run many queries on the server or restart sql server , next incremental it hangs and nothing is locking tables. but when it hangs it looks like it's in a deadlock .
so the only way to move forward is to restore back the datamart and wait for another incremental .
I tried tuning advisor , profiler ...
same ETL was working before on different server perfectly when we change it to 2 xQuad core processor and 16 GB RAM this problem started !!!
Thanks and Best Regards
Nassim
September 22, 2008 at 6:04 pm
THat rings a bell - 64 bit O/S as well?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 26, 2008 at 8:42 am
it's 32 bit but /3gb /PAE enabled
Regards
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply