Large log file can bring SQL Server down when transaction log shipping runs
We were having very poor performance when we turned on transaction log shipping on our SQL Server. We are using SQL Server 2005. The transaction log file was around 30 GB because the database was in Full Recovery mode. The server became very slow, every 15 mins when we were doing the log shipping, it used to become very slow and sometimes nonresponsive. The event log was getting full of SqlTimeout exceptions generated by the web site. The web site started to show asp.net error page very frequently. We could not use SQL Server Management Studio to login to SQL Server so that we could do something about it.
Here's how the connection time was reported from an external monitoring site:
The peaks are 30 seconds which mean they timed out.
So, here's what we did:
- Turned off Log shipping
- Restarted SQL Server.
- Switched Database to Simple recovery model. Shrunk the log file. This made the log file come down to couple of megabytes.
- Ran for some days. All looked ok.
- Then switched DB to Full Recovery model and configured log shipping again.
So far running fine. But we go down for an hour every Saturday when we run INDEX DEFRAG on the indexes. The log ships show around 5 or 6 log backups which are each 1 or 2 GB in size when the index defrag happens.