none
Disable Transaction Log growth.

    Question

  • 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

Answers

All replies

  • You cannot turn off TLOG growth

    Please read the blow links

    http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx   ---Minimal Logging changes in SQL Server 2008


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 18, 2010 8:17 AM
    Answerer
  • 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

    http://sqlblog.com/blogs/tibor_karaszi/archive/2010/10/14/why-we-never-want-to-trick-sql-server-into-rebuilding-a-log-file.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 18, 2010 8:45 AM
    Answerer
  • 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.


    Corby
    Wednesday, December 28, 2011 8:18 PM