Transaction log keeps growing

Hot Topic

repeatwith property
sql stored procedure question
Dynamically edit DataSet
Restoring DB hangs Windows XP SP 2
yyyymmdd text data into a datetime field in SQL Server?
Where is the Business Intelligence Development Studio
I can not enter non english text in SQL express...
SSIS Package failing to disconnect?
SQL 2000 to SQL 2005 works for one 2000 server but not the next
Can't Connect to Reportserver using Management Studio

SQL Server

How to.
Reporting Services 2005 : The report server cannot open a connection to the report server database
SQL Server 2005 Stored Procedure inserting duplicate keys into table that is uniquely indexed
Numerous (every 10 minutes) Event log entries
Failed to generate user instance and Cached Domain Login....
Xpath/Xquery SQL question
Problem with Linked Server
Using Sql Express databases with Windows Forms applications
Copying table data from SQL Server 2005 to SQL Server 2000 - Very Slow when using OLEDB Source and Destination sources?
Multiple inserts against single insert from temp table

Transaction log keeps growing

I have set the dboption 'trunc. log on chkpt.' to true; yet the transaction log keeps on growing.

sp_dboption pubs_db

does show 'trunc. log on chkpt.' to be set.

I tried

DBCC SHRINKFILE(pubs_db_log,100)

and for a while, the pubs_db_log.ldf was about 100M and then it grew to 10GB.

I have a process running that is inserting more than 15K rows per hour and I would like to keep the transaction log from growing. How do I control the tx log growth



Answer this question

Transaction log keeps growing

  • Zvonimir

    I Too... did you know to decide the problem

  • Matt Fraser

    What does the "log_reuse_wait" column from sys.databases say

    thanks,
    mark

  • Ricardo Oliveira

    Jason -
     Did you have a chance to analyze the problem and collect the data we asked for < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

     Please let us know your answers to the questions asked in Mark’s and my e-mail.  
    Thanks
    Mirek

     



  • admeralthrawn

    Jason -

    I have few questions:

    1)       What version of SQL Server are you using

    2)       What is your recovery model

           (if you use SQL Server Beta2 or CTP15 look for “recovery_model_desc”
             in sys.databases and indicate “log_reuse_wait” as well)

    3)       Do you have any long-running transaction .
     Are you inserting all rows in one transaction or in multiple transactions

    4)       Why do you want to shrink the log . This may look like a silly question, but shrinking  the log could be expensive, since you need to grow it later.

    Also I would like you to do the following.

      Could you please run dbcc sqlperf (logspace) and let me know the log size, log space used%
      and status for your database after you completed your inserts.

    Thanks

    Mirek



  • davidhhhhhhhhhhhhhh

    Just some ideas I'm not an expert.  Does the 15k rows per hour actually account for the 10GB log   Sounds excessive, maybe there are other major db changes going on such as re-indexing at regular intervals.  If your database is in FULL recovery mode re-indexing would be logged and would cause a big spike which would explain the Max file size.

    I'm not sure that it is not truncating the log.  I assume you are going by the file size.  What if it is truncating the log but the file size simply represents the log contents for the period of heaviest activity since the last shrink.  In that case shrinking the file may not be the solution so much as tolerating it at 10Gig so long as it settles down around that size.

    Let me know what you find out and good luck

  • Transaction log keeps growing
Answer this question