?
Solved

Transaction Log keeps growing even continue to run Backup Log with Truncate_only on DB

Posted on 2014-12-23
12
Medium Priority
?
137 Views
Last Modified: 2015-01-03
On my MS SQL 2005 test db server, I made a change to use a store procedure to update about 35K records in the database.   The transaction log kept growing when the procedure was run until I ran out of disk space (Log file grew more than 250GB).   I set the database recovery mode to Simple and scheduled backup of log using "with Truncate_only" to happen every five minutes, but that didn't stop the log from growing.  Any suggestion on how to stop the log from growing?  I can't modify the store procedure which came in on a commercial product that is locked.   I like to use this procedure to update the db, so I won't risk introducing any database integrity issues into the db.  

Thank you very much in advance for your help.
0
Comment
Question by:altonyoung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40515216
If the recovery model is simple, you can't do log backups.

But the log will automatically truncate at every checkpoint, which is roughly every 5 mins or so, depending on your server load and other internal factors and settings.

You need to run:
DBCC OPENTRAN
on the db is see if something is keeping the log from being truncated.

You also need to look at sys.databases row for that db and see what the "log_reuse_wait_desc" has in it.  For the log to truncate, it needs to be "NOTHING"; anything else and the log can't truncate.
0
 

Author Comment

by:altonyoung
ID: 40515375
ScottPletcher,

Thanks for the quick reply.   Does this mean if there's update statement that the procedure continues to run, hence keeping an open traction on the database, then I won't be able to truncate the log?
0
 

Author Comment

by:altonyoung
ID: 40515443
Sorry.   I meant reuse the log space even in Simple recovery mode.   Thanks.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40515543
Yes.  The log is a sequential / "flat" file.  Any record that is still in use prevents clearing anything from that record forward.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40516098
I set the database recovery mode to Simple and scheduled backup of log using "with Truncate_only"
Would like to know how you did that since in Simple Recovery model you aren't allow to perform a transaction log backup.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40516478
@Vitor:

That was the first sentence of the first reply :-).
0
 

Author Comment

by:altonyoung
ID: 40516606
I restored the database, set recovery mode to Full and try it again.  

Here is the result of dbcc opentran:
Oldest active transaction:
    SPID (server process ID): 58
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (36457:129703:175)
    Start time    : Dec 24 2014  7:42:26:023AM
    SID           : 0x01050000000000051500000077026014a1776d5cc811bf7b72080000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Also sys.databases show the db log_reuse_wait_desc is ACTIVE_TRANSACTION.

Looks like even with Full recovery mode and backup log with truncate_only every five minutes and I am still not able to reuse the log space.   Any idea what I can do, so I can reuse the log space, so my LDF file won't continue to grow until I am out of disk space?  

Thank you.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40516622
That transaction isn't that old:
Dec 24 2014  7:42:26:023AM
so your log space should be truncating fine.

Keep in mind that the log file won't shrink -- get physically smaller on disk -- until and unless you explicitly shrink it (DBCC SHRINKFILE).  But SQL will truncate the log -- mark the log records as "reusable" -- as soon as it can.

In simple recovery, "as soon as it can" is immediately after the transaction is committed (barring other things such as replication, CDC, etc.).  In other than simple recov, the trans must be committed and that record in the log must have been backed up (BACKUP LOG ...) before it can be truncated.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40520998
Looks like even with Full recovery mode and backup log with truncate_only every five minutes
Not the answer to your quesion but: Either use Simple Recovery Model or do a Transaction Log backup.  Using Full Recover Model and doing a backup log with TRUNCATE_ONLY is pointless and dangerous (you may actually think you can do a point in time restore).
0
 

Accepted Solution

by:
altonyoung earned 0 total points
ID: 40521995
For some reason I just can't keep the log small even using the Simple recovery model.  The tempdb log also grows to over 50GB.    My work around was to figured out a way to manually update the database tables instead of using the store procedure.  Lots of work, but I got it done.  

Thank you all for your response.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40522016
>> For some reason I just can't keep the log small even using the Simple recovery model. <<

SQL still has to log transactions, and the log space must be kept until the transaction is committed.

If your stored procedure has its own "BEGIN TRANSACTION" statement, then you might be able to reduce the log space needed at one time by issuing a "COMMIT TRANSACTION"(s) sooner or more often.  It sounds like that may be the case if running it outside the stored proc uses less total log space.

But if a single INSERT or UPDATE statement is causing the logging, there's no way you can reduce the size of that except to do the INSERT or UPDATE in smaller batches.
0
 

Author Closing Comment

by:altonyoung
ID: 40528934
ScottPletcher,  since you are the first one the respond and provided useful information about recovery model and transaction logs, I will award you the 500 points.    Thank you.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question