We came across issue of database transaction log is full on production server, users were getting the timeout error while accessing company website. There was no monitoring tool placed for sql server to keep a eye on transaction log that is bad. May be they don’t care much about database, Issue raise to DBA , we started to investigate the issue.
We checked the sql log and event viewer log, Issue was with transaction log as alert log suggests ……. transaction log is full of critical production database.
We surprise to see the transaction log file size that was grown up to 100 GB more than expected size and equal to total disk capacity.
Basic idea here to reduce the memory pressure so that users can start working and accessing the company’s website.
so we decided to take transaction log backup before disturbing the database because if anything goes wrong we should recover back to normal. Backup took almost 20 to 30 minutes to complete once backup finish successfully we tried to shrink the log file.
T-SQL commands for truncating log
BACKUP LOG db1 WITH TRUNCATE_ONLY
Note : This will brake sql server transaction log chain that will not allow to restore your database to point in time in case of any issue. Advise to take full backup after truncating transaction log.
Shrinking will remove the unused space from log file In this case it reduced to 20 GB which is normal to company prospective.
This was worked and problem got resolved.
Recommendation to backup transaction log frequently to keep transaction log in good shape.
Shrink and truncating transaction log file in sql server 2008: http://www.sqlserver-query.com/sql-server-2008-shrink-database-and-truncate-transaction-log-file/