Post reply

Moving around Data and Log Files

  • October 20, 2009 at 2:21 pm

    #205807

    Folks,

    In order to overcome the space issues, we moved the data and log files of databases around such that the space was optimized but we made sure that the data and log files are in different drives. While this saved us space, it caused our ETL Loads to run forever.

    We are not sure if the moving around of data and log files caused our ETL loads to run awfully slow.

    We tried rebuilding the indexes etc but didn't help much. What could have gone wrong here?

    Any help or suggestion in this direction would be highly appreciated.

    Thanks in advance.

    Amol

    Amol Naik

  • October 20, 2009 at 3:10 pm

    #1068545

    Where do ETL loads store temporary files? Is it SSIS? Are they running on the DB server?

    Regards

    Piotr

    ...and your only reply is slàinte mhath

  • October 20, 2009 at 4:00 pm

    #1068571

    Piotr,

    What do you mean by temporary files? ETL is implemented using SSIS and the packages are running on a separate server.

    Thanks,

    Amol

    Amol Naik

  • October 20, 2009 at 4:12 pm

    #1068574

    Ok, if it a separate server, it doesn't matter. SSIS creates temporary files for storing blobs or temporary data buffers on disk. If you don't change it, it is usually not the best disk you would choose - C:.

    Did you look at performance counters on the DB server? Especially IO, Avg disq queues for data and log drives?

    Are the tables that you load data to indexed? What is fragmentation of the indexes?

    Regards

    Piotr

    ...and your only reply is slàinte mhath

  • October 20, 2009 at 4:23 pm

    #1068582

    It doesn't use any blobs. The tables are indexed, as i mentioned in my initial post, we did perform a index rebuild.

    Looking at the perf counters did not give us indication of any disk bottlenecks.

    Thanks,

    Amol

    Amol Naik

  • October 21, 2009 at 11:19 am

    #1069015

    We moved the data and log files back to where they were originally and the Job behaviour returned to normal and completed like it did before we moved around the data, log files.

    So all is good and we are super happy !

    Thanks,

    Amol

    Amol Naik

Viewing 6 posts - 1 through 5 (of 5 total)

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