1

I'm attempting to troubleshoot an issue with one of our customers who's using SQL Server 2005 SP3 Express to house their application data. The automatic backups that we perform when upgrading our application are timing out after 30 minutes, and I've been sitting and watching the backup take place in SSMS for about 20 minutes now and it's only gotten to 30%.

The database is only slightly over 1GB, so I'm baffled as to what could be causing this sort of horrible performance. The machine is a 1.87GHz Xeon with 3GB of RAM running Windows Server 2003 R2. While that's hardly a powerful box, this seems ridiculous.

Does anyone have any idea why these backups could be taking so long and, more importantly, how I can do something about it?

Adam Robinson
  • 370
  • 3
  • 6
  • 19

1 Answers1

1

Make sure that you have the backup directory excluded from any anti-virus software running. Monitor the machine for other cpu-hogging or memory-hogging applications, if your memory is overcommitted, that would slow things down. But a backup that size should only take minutes unless there is a hardware problem or seriously slow disks.

If it is "timing out", what error are you getting?

SqlACID
  • 2,176
  • 18
  • 18
  • By timing out, I'm referring to our in-application connection using the .NET SQL client, not an error that the server returns. The backup I was running in SSMS completed in about 45 minutes or so. – Adam Robinson Mar 04 '11 at 14:37
  • 45 minutes to back up a 1GB database? Where are you backing it up to? – joeqwerty Mar 04 '11 at 15:15
  • @joeqwerty: Just the ordinary backup directory on the same local drive as the database. It seems that all other database operations are monumentally slow, as well (took about another 45 minutes to delete ~180k rows from three different tables). – Adam Robinson Mar 04 '11 at 15:40
  • @Adam Robinson : long deletes from tables are a completely different issue, could be dependent on table size, indexing, locking. But slow disk could account for both. Can you run perfmon and see what disk queue length is on the disks? Can you check the max server memory value in sp_configure or SSMS? – SqlACID Mar 04 '11 at 16:13
  • I know they're different, but I figured it was worth noting. Looking at perfmon over the last couple of minutes, Pages/sec and Avg. Disk Queue Length are both spiking. Pages/sec has a max of 1051 and average of 36. Disk queue has a max of 118 and an average of 10 at a scale of 100 – Adam Robinson Mar 04 '11 at 16:36