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_transac
2. (SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_h
andle)) 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?