Solved

Size of SQL server Log File increasing exponentially.

Posted on 2014-11-04
3
232 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
3 Comments
 
LVL 39

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now