vikasbapat
asked on
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_transac
2. (SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_h
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?
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
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
a). Database recovery mode and database log backup - Daily with scheduled jobSince 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 spaceIf 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.
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.