Disable Transaction Log growth.


  • Hello,

    it is possible to turn off Transaction Log growth in SQL Server 2008 R2? The problem is that we have low disk space. And i need Load big table, which have a lot of columns and almost on all are indexes. So load is failing because of transaction Log. O set recovery model to Simple.

    I think only about one solution.


    1) Drop all indexes on destination table.

    2) Load data to table.

    3) Recreate indexes.


    I noticed, that when i load data to table which hasn't any indexes transaction log isn't growing.

    Any other ideas?

    Monday, October 18, 2010 8:13 AM


All replies

  • You cannot turn off TLOG growth

    Please read the blow links   ---Minimal Logging changes in SQL Server 2008

    Best Regards, Uri Dimant SQL Server MVP
    Monday, October 18, 2010 8:17 AM
  • Well, then another question :)

    It is possible to delete Transaction Log? I mean DW_Log file. Or then database will have erros?

    Monday, October 18, 2010 8:35 AM
  • Yes it is possible, but then your db will be corrupted. I think  your only the option is to add more disk space to handle loading...

    Also,  you may want to read

    Best Regards, Uri Dimant SQL Server MVP
    Monday, October 18, 2010 8:45 AM
  • The simplest way is to set your database recovery model to simple and then shrink the Log.  However if you do this you will lose the ability to recover to a Point In Time...
    Please click "Mark As Answer" if my post helped. Tony C.
    Monday, October 18, 2010 8:53 AM
  • SQL Server should have an option to turn logging off.

    Some applications do not need recovery of a point in time.

    Our application is a crawler, and there are two tables for every table in our database, if a run does not complete for any reason such as a power failure or any other reason a run did not complete, the data would be corrected the next time the app runs, the data is only temporary, and writing to the log is a huge waste.

    Our crawler executes about 500,000 to 1,000,000 stored procedures for reach run, which takes about 5 to 8 minutes in our test environment, in the real world it could be tens of millions of stored procedures executed each run, 24 hours a day.

    Logging is completely unnecessary, a waste of resources and a better designed database system would include an option to turn off logging.

    I am pro Microsoft devoper, but some of the decisions made on my behalf do not suit the needs of our application.

    Wednesday, December 28, 2011 8:18 PM