Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

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. –  MattK Jun 24 '10 at 19:13

3 Answers 3

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.

share|improve this answer
    
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. –  MattK 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. –  MattK 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.

share|improve this answer
    
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. –  MattK 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?

share|improve this answer
    
No AV on these servers. –  MattK Jun 23 '10 at 23:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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