If you are experiencing site outages that occur intermittently but regularly and which resolve on their own, check your server logs for the following error:
A network-related or instance-specific error occurred while establishing a connection to
SQL Server. The server was not found or was not accessible. Verify that the instance name
is correct and that SQL Server is configured to allow remote connections. (provider:
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) --->
System.ComponentModel.Win32Exception (0x80004005): The network path was not found
You can verify that your SQL Server instance has no downtime corresponding with the site downtime using your SQL server logs.
If there is server downtime corresponding with site downtime, then the SQL server will need to be examined separately by your DBA.
If there is no server downtime corresponding with site downtime, you should check to see if your database backup windows correspond with site downtime. Server backups, especially in larger instances, are incredibly I/O-intensive. If they aren't carefully managed, they can cause downtime during your server's maintenance period.
Checking backup dates and times
You can check to see whether you have had previous backups that overlap your site downtime by running the following SQL query in your IDE of choice:
SELECT bs.server_name AS ServerName,
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008 or SQL Server 2008 R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END AS ServerVersion,
bs.database_name AS DatabseName,
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Database Differential'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS BackupType,
bs.backup_start_date AS BackupStartDate,
bs.backup_finish_date AS BackupFinishDate,
WHEN 2 THEN 'Disk'
WHEN 5 THEN 'Tape'
WHEN 7 THEN 'Virtual device'
WHEN 105 THEN 'A permanent backup device'
ELSE 'Other Device'
END AS DeviceType,
bmf.physical_device_name AS PhysicalDevice,
bs.backup_size/(1024*1024) AS [BackupSize(MB)],
bs.compressed_backup_size/(1024*1024) AS [ConmpressedBackupSize(MB)]
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.backup_start_date ASC
Pay special note in the results to the backup size and start/finish datetime.
If you have overlap between your outages and maintenance windows, it's likely that the maintenance windows are involved in the site outages.
Checking and disabling your future backup schedule
If your server backups are done via a third-party program, you'll need to check that program to find the schedule and compare. If they are done via SQL Server Management Studio, you can check them using the following steps:
- Go to Object Explorer > [Server tree] > Maintenance Plans > [Your backup plan's name]
- A schedule summary should appear there. You can double-click on the portion of your maintenance plan that includes the backup operation to get a summary of your schedule timing
- You can test whether turning off these maintenance windows clears up your problem by using the Unschedule button () to turn off automatic maintenance and set the maintenance job to run on-demand only.
NOTE: Changes to your server maintenance should be made only after consultation with your DBA. Turning off your automatic backups may not be a long-term solution for your site.
If turning off regular maintenance clears up your site outages, then your DBA will need to review the maintenance processes. Outlining all possible reasons for site maintenance problems goes beyond the scope of this article. However, Microsoft has put out several extensive knowledge base articles and books about troubleshooting SQL maintenance problems. This article from Microsoft is a good place to start both for making our maintenance operations more efficient and for finding other possible causes for this problem: Troubleshooting SQL Server backup and restore operations.