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.