Solved

Size of SQL server Log File increasing exponentially.

Posted on 2014-11-04
3
352 Views
Last Modified: 2015-03-08
We are facing the stated issue with details as follows:
A ticketing application in JAVA and Hibernate technology with frequent update/insert and deletes.

All queries processing happen via hibernate (no stored procedures).

Size of SQL server Log File increasing exponentially.

We are facing this issue from last 3~4 weeks.We have already created a schedule job for log file Backup every day. Still we have noticed daily increase in file size. We also checked for any open transactions remaining in database, but didn't found any. Server is configured with 48GB RAM, 8 cores and total 1 TB disk space spanned on two hard drives. Total 2 databases currently on server

With simple recovery mode for database with high frequency updates.
With all search on internet, We found following suggestion from links like this http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space
and many more with lot of good information and knowledge.

In line with that we checked for followings things
a). Database recovery mode and database log backup - Daily with scheduled job
b). Open transaction if any - No open transaction, with queries on log file records with
      1. (select * from  sys.dm_tran_active_transactions)
      2. (SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0)
      3. (Select * from ::fn_dblog(null,null))
c). Long running transactions - No long running transactions, with queries on http://sqlserverplanet.com/dmvs/find-queries-taking-most-cpu-processor

Because of above situation, after log backup we shrink the log file as its only option because log file size grows so fast and can go out og disk space and we don't left with space
to backup the log itself. We also understand with our search its not good practice but its current answer to current situation.

Question is, In situation given above, how to keep the transaction log file size in control or of fix length ? and what are best practice to follow as DBA in this situation for going forward?
0
Comment
Question by:vikasbapat
[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
3 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 40421579
I assume you have recovery model set to FULL and I would switch it to BULK-LOGGED that will offer the same FULL+T-Log backup scenario.

I would increase the T-log backup to much more than once per day to release the log space back to be used instead of just having the T-log growing.

Ultimately if none of the above help - although it should - you can switch your backup/restore scenario to a Differential instead of FULL+T-Log backups that could be done under the SIMPLE recovery mode of your DB which offers the least amount of database logging from all three available recovery models.

I would also try to figure out from the APP point of view why "We are facing this issue from last 3~4 weeks"? Was there any change in the environment(deployments, configurations, etc.) or just much more activity/load all of a sudden? If is just load one of the above scenarios should do otherwise someone may need to look at the new code/config changes and determine their impact and relation to the excessive INSERT/DELETE/UPDATE-s.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40421686
I would disagree with the option to set to Simple recovery mode if this is a production database and the ability to perform point in time recovery up to the time of any failure is a concern to you.

If you switch to Simple Recovery mode, your ability to recover from a failure is limited to the point in time where you took your last backup.

Taking log backups more frequently should help you greatly.  If your log growth is so fast then look at taking log backups every hour (or thirty or fifteen or five minutes - depending upon your transaction volume and how critical your tolerance to data loss is).

That really should address your problem.  The ability to reuse space in the transaction log only happens after the transaction log is backed up.  If you're only doing that daily, then by the end of the day, you're going to have a large log file.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40421868
a). Database recovery mode and database log backup - Daily with scheduled job
Since you tlog is growing quickly a single tlog backup isn't enough. Don't be afraid to execute a tlog backup every hour or even every 15 or 30 minutes. Will help to keep the tlog size small.
Because of above situation, after log backup we shrink the log file as its only option because log file size grows so fast and can go out og disk space and we don't left with space
If you proceed as suggested above you won't need to shrink the tlog file. Also check if it's growing in percentage or with a constant size (recommended).
Question is, In situation given above, how to keep the transaction log file size in control or of fix length ? and what are best practice to follow as DBA in this situation for going forward?
As I said before. Just don't keep the backups in the same disk as tlog files.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 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