I have interited a box running SQL Server 2008 and Windows 2003, and have had a few events where largeish (35GB) log backups "stall", both before and after the installation of SQL 2008 SP1. The server log ships to a standby, so regular log backups are taken at 15 minute intervals.

However, after an index reorg causes the log to grow to about 35GB (on a DB with about 17GB of data), the next log backup runs to ~95% completion, then seems to stop. The process shows as suspended, with a wait state of BACKUPIO. CPU, read, and write activity on the SPID also does not change, and the process stays in this state for hours, when normally a backup of this size should complete in about 20 minutes.

This server has a single RAID-1 volume, thus the source database files and destination backup files are on the same volume. However, I cannot determine if another process is blocking the backup. The backup SPID cannot be killed, and the only way to terminate the log backup and clear the lock on the backup file is to cycle the SQL Server service.

There was one event where the backup terminated completely, with an error that another process had locked the backup file, but no details about what that process was.

Can anyone suggest a cause or diagnostic process to this situation?

migrated from stackoverflow.com Jun 15 '10 at 9:30

This question came from our site for professional and enthusiast programmers.

  • Have you tried to write a manual CHECKPOINT to the database before running the backup? – user3914 Jun 20 '10 at 2:46
  • Just tried that. Same problem. – kermatt Jun 24 '10 at 19:13

Once the stall has occured use a tool such as process explorer to determin if other processes are affecting the access to the file i.e. locked the file.

  • We tried that, and the "handles" utility as well, with negative results. Note that in only one case was a file lock error generated, in all other cases, the backup simply stalls. – kermatt Jun 18 '10 at 17:12
  • What does your index reorg look like? IO functions can be affected by the fill factor chosen. You can also try switching to bulk-logged mode during your reindex. sql-server-performance.com/tips/rebuilding_indexes_p1.aspx – James Santiago Jun 20 '10 at 2:28
  • In this test, the Maintenance Plan Wizard is generating the ALTER INDEX ... REORGANIZE statements. We also have a custom procedure which only reorgs indexes above a certain average fragmentation threshold. I would rather not have to change logging modes on a live database to perform maintenance. – kermatt Jun 23 '10 at 23:07
  • Well, you should probably look into building your own index reorg script with different options. There are also some logs created by the maintenance plan wizard you should review. Here, this might help: mssqltips.com/tip.asp?tip=1367 – James Santiago Jun 24 '10 at 6:25

Can it be it takes that long to ship the generated log? BackupIO also inclodes network latencies during network operations related to IO.

  • In this case the shipping schedules are paused, to make sure there is no conflict between the backup, copy, and restore jobs. Thus the backup job is the only one in play, and it never makes it to the stage where the file is sent across the network. As a test, I tried a direct BACKUP LOG command in SSMS, and observed the same behavior. – kermatt Jun 15 '10 at 15:25

This is likely a shot in the dark - but I have seen this happen when anti-virus software on the server begins scanning the backup file during the backup. Is there anti-virus software running on the server, or if there is, is it set to exclude backup file extensions or volumes containing the backups?

  • No AV on these servers. – kermatt Jun 23 '10 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.