locked
Database log file growth RRS feed

  • Question

  • Hi

    Please assist me from huge log file growth while loading data into database from mainframe source.

    Database log file has been grown from 10 gb to 60 gb after data were loaded (Date loading happen for 4 hrs). we are also taking log backup for every half an hour.

    Database recovery model is BULK_LOGGED. we dont want to put db in Simple recovery model, because it is production db.

    Earlier the database was in FULL recovery model. Due to this issue we have changed to BULK_LOGGED.

    I have checked why the log was not reusing while data loding using the below query.

    select log_reuse_wait_desc from sys.databases where name = 'mydatabasename' This query has been displayed everytime as "ACTIVE_TRANSACTION".

    Please let me know how can I restrict log file size from growing hugely in this scenario..

    Thanks in advance...!

    Regards..                                                                                                                                                Su.

    Monday, September 12, 2011 10:33 AM

Answers

  • Hi ,

            Please verify the transactions in your loading process.. Because it is a long running process, smaller transactions would be helpful. You need to phase wise commit of the transaction dpends on your process feasibility.


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Monday, September 12, 2011 11:59 AM
  • Hi ,

              If the logic in the triggers is more , then obviously the process will take more time ... I would suggest the same should be achieved using some alternate plan, other than using triggers. One possible option is to load the data first using the bulk processs and then initiate the process of loading data into the other tables ( as used in triggers).

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Tuesday, September 13, 2011 9:55 AM
  • Replication between the server which has TableB to the another server with TableC, does have the same impact as of the earlier replication. Hence the scenarios will be same.
    Friday, September 30, 2011 6:32 AM

All replies

  • Hi ,

            Please verify the transactions in your loading process.. Because it is a long running process, smaller transactions would be helpful. You need to phase wise commit of the transaction dpends on your process feasibility.


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Monday, September 12, 2011 11:59 AM
  • Reddy Balaji C,

    Thanks for you quick reply

    I have forget to mention here a point in the earlier post. we have been implemented triggers for Insert statements. Before enabling triggers the log growth is very small that is recommended as per my company policy. But here the scenario is we need to enable the triggers while data loading. After enabling these triggers the log growing hugely (log size before dataload is 15 GB, while data loading log size is crossing 80 GB at the same time data file size is 103 GB where as data file size before data loading is 98 GB).

    Here data will be loaded to another server through linkedserver and into the replica tables in the same source server and database.

    Please assist me for this issue.

    Regards

    Sureddy

     

     

    Monday, September 12, 2011 2:11 PM
  • Please respond anyone with best sollution...

    Thank you

     

    Regards

    Sureddy

     

     


    Sureddy
    Tuesday, September 13, 2011 8:26 AM
  • Hi ,

              If the logic in the triggers is more , then obviously the process will take more time ... I would suggest the same should be achieved using some alternate plan, other than using triggers. One possible option is to load the data first using the bulk processs and then initiate the process of loading data into the other tables ( as used in triggers).

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Tuesday, September 13, 2011 9:55 AM
  • Thanks Balaji..

    I will test with your suggestions and get back to you..

     

    Regards

    Sureddy

     


    Sureddy
    Tuesday, September 13, 2011 11:06 AM
  • Hi

    Here we have implemented insert triggers to store data into two different destinations.

    one is within the same databases tables (Repli tables)

    Second one is within the different server.

    Let me explaine with example

    TableA - where data is loaded directly from the source.

    TableB - Repli table within the same server and database (Data will be loading from TableA using insert trigger).

    TableC - Another table from different server, we have implemented Linked server between two servers (Data will be loading from TableA using insert trigger)

    When I disable the triggers (For TableB and TableC) it wont effect the log growth.

    Today we have tested again data is loading into the data file very slowly...

    How the log will be acting if I create trigger on TableB to replicate changes to TableC??

    Are these both scenarios are same ???

    Thanks for your quick reply

    Regards

    Sureddy

     

     


    Sureddy
    Tuesday, September 13, 2011 11:31 AM
  • Replication between the server which has TableB to the another server with TableC, does have the same impact as of the earlier replication. Hence the scenarios will be same.
    Friday, September 30, 2011 6:32 AM