Post reply

Data warehouse load job hangs

  • February 16, 2007 at 4:52 am

    #92971

    I have a SQL Agent job that runs a data warehouse load each morning.  It transfers tables from various systems into staging areas, then runs stored procedures to transfer the data from the staging areas into the warehouse.  This has been running fine for a few years.

    This morning, the job was still running a procedure to transfer one of the tables at 10:30 and it had started that procedure at 07:43 according to the log.  No error was generated, it was still processing it.  Normally this one table takes just short of two minutes to load.

    There were no locks anywhere, it is not a transaction log issue (i.e. size/running out of disk space) (the databases all use simple recovery model and truncate and dbcc shrinkfile the log after each load anyway).  There is no error generated, it just keeps going.  I can't see anything in the logs.

    It ran fine yesterday.  Does anyone have any ideas that might help?

  • February 18, 2007 at 6:11 pm

    #690387

    Suggestions:

    1. Verify the network is OK by PING to the servers.
    2. Stop and start the services including MSSQLServer and SQLSERVERAGENT
    3. If #2 did not work, then reboot the server that runs the job.

  • February 20, 2007 at 2:40 am

    #690596

    Thanks.  I found the problem: I'd changed a UDF which caused serious performance problems.  Quickly changed back.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply