Size of SQL server Log File increasing exponentially.

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?
vikasbapatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve WalesSenior Database AdministratorCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.