SQL Job Hung. When destination had insufficient disk space

wchandlerwchandler Posts: 7
edited September 21, 2016 12:17PM in SQL Backup Previous Versions
SQL - SQL 2008 R2 Standard SP3
Windows - Server 2008 R2 Enteprise
RedGate Backup - 6.4.0.56


Hello all,

I had a strange issue with a server last night (19/09/2016), where the SQL Job for the backup and hung and didn't report a failure. I was wondering if anyone else had seen this before.

There was a disk space issue on 16/09/2016 where the backups get placed.

This was resolved and most other backup jobs failed gracefully and picked up again as scheduled.

however I had two jobs (a Transaction log backup and a full backup) both just hang - and by this i mean just sat there in a executing state and never failed or stopped. This caused the transaction log to fill up and we were only alerted to the issue when the disk space was low.

Manually killing the job caused the RedGate monitor to finally give the following error log:
SQL Backup log file 6.4.0.56
 
-SQL "BACKUP LOG [MYDATABASE] TO DISK = '\BACKUPSERVERLogBackupsMYSERVER01<database><AUTO>.sqb' WITH ERASEFILES_ATSTART = 36h, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 2, THREADCOUNT = 3 "
 
-----------------------------  ERRORS AND WARNINGS -----------------------------
 
 
16/09/2016 22:18:00: Backing up MYDATABASE (transaction log) to: 
16/09/2016 22:18:00:   \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb
 
16/09/2016 22:18:01: Deleting old backup file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160915_081801.sqb
16/09/2016 22:18:02: BACKUP LOG [MYDATABASE]  TO VIRTUAL_DEVICE = 'SQLBACKUP_9DFFCDAD-2107-4CC0-B3EC-1F6403EF7DDA', VIRTUAL_DEVICE = 'SQLBACKUP_9DFFCDAD-2107-4CC0-B3EC-1F6403EF7DDA01', VIRTUAL_DEVICE = 'SQLBACKUP_9DFFCDAD-2107-4CC0-B3EC-1F640
3EF7DDA02' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (MYDATABASE), 16/09/2016 22:18:00', DESCRIPTION = N'Backup on 16/09/2016 22:18:00  Server: MYSERVER01  Database: MYDATABASE', 
 FORMAT
 
16/09/2016 22:25:34: Thread 0 error: 
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
16/09/2016 22:25:34: Warning 210: Thread 0 warning: 
WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb at position: 5142217728
16/09/2016 22:20:33: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:21:03: Re-attempt: 1
16/09/2016 22:21:03: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:21:33: Re-attempt: 2
16/09/2016 22:21:33: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:22:03: Re-attempt: 3
16/09/2016 22:22:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:22:34: Re-attempt: 4
16/09/2016 22:22:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:23:04: Re-attempt: 5
16/09/2016 22:23:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:23:34: Re-attempt: 6
16/09/2016 22:23:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:24:04: Re-attempt: 7
16/09/2016 22:24:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:24:34: Re-attempt: 8
16/09/2016 22:24:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:25:04: Re-attempt: 9
16/09/2016 22:25:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:25:34: Re-attempt: 10
16/09/2016 22:25:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:25:34: 
16/09/2016 22:25:34: Thread 1 error: 
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
16/09/2016 22:25:34: Warning 210: Thread 1 warning: 
WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb at position: 5142217728
16/09/2016 22:20:33: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:21:03: Re-attempt: 1
16/09/2016 22:21:03: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:21:33: Re-attempt: 2
16/09/2016 22:21:33: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:22:03: Re-attempt: 3
16/09/2016 22:22:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:22:34: Re-attempt: 4
16/09/2016 22:22:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:23:04: Re-attempt: 5
16/09/2016 22:23:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:23:34: Re-attempt: 6
16/09/2016 22:23:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:24:04: Re-attempt: 7
16/09/2016 22:24:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:24:34: Re-attempt: 8
16/09/2016 22:24:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:25:04: Re-attempt: 9
16/09/2016 22:25:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:25:34: Re-attempt: 10
16/09/2016 22:25:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:25:34: 
16/09/2016 22:25:34: Thread 2 error: 
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
16/09/2016 22:25:34: Warning 210: Thread 2 warning: 
WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb at position: 5142217728
16/09/2016 22:20:33: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:21:03: Re-attempt: 1
16/09/2016 22:21:03: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:21:33: Re-attempt: 2
16/09/2016 22:21:33: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:22:03: Re-attempt: 3
16/09/2016 22:22:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:22:34: Re-attempt: 4
16/09/2016 22:22:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:23:04: Re-attempt: 5
16/09/2016 22:23:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:23:34: Re-attempt: 6
16/09/2016 22:23:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:24:04: Re-attempt: 7
16/09/2016 22:24:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:24:34: Re-attempt: 8
16/09/2016 22:24:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:25:04: Re-attempt: 9
16/09/2016 22:25:04: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (There is not enough space on the disk.)
16/09/2016 22:25:34: Re-attempt: 10
16/09/2016 22:25:34: WriteFile failed for file: \BACKUPSERVERLogBackupsMYSERVER01MYDATABASELOG_(local)_MYDATABASE_20160916_221800.sqb (The handle is invalid.)
16/09/2016 22:25:34: 
16/09/2016 22:25:36: SQL error 3013: SQL error 3013: BACKUP LOG is terminating abnormally.
16/09/2016 22:25:36: SQL error 3271: SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_9DFFCDAD-2107-4CC0-B3EC-1F6403EF7DDA:" 995(failed to retrieve text for this error. Reason: 15105).
16/09/2016 22:25:36: SQL error 3202: SQL error 3202: Write on "SQLBACKUP_9DFFCDAD-2107-4CC0-B3EC-1F6403EF7DDA" failed: 995(failed to retrieve text for this error. Reason: 15105)
16/09/2016 22:25:36: SQL error 3202: SQL error 3202: Write on "SQLBACKUP_9DFFCDAD-2107-4CC0-B3EC-1F6403EF7DDA" failed: 1117(failed to retrieve text for this error. Reason: 15105)

Has anyone had this before? Is there a way to prevent this from occurring again?


Here is the SQL for the Job:
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "BACKUP LOG [MYDATABASE] TO DISK = ''\BACKUPSERVERLogBackupsMYSERVER01<database><AUTO>.sqb'' WITH ERASEFILES_ATSTART = 36h, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 2, THREADCOUNT = 3"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END


Thanks in advance.
Wayne.
· Share on Twitter

Comments

  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi, thank you for your forum post.

    You do know that this version of SQL Backup is now over 5 years old, first released in March 2011. The current latest version is V8.0.3.

    There are various reasons why SQL backup become unresponsive. Ideally it should have failed reporting the out of disk space error which it finally performed once you stopped the SQL Backup Agent service.

    Now that you have most likely restarted the service, it is impossible to work what has occurred.

    If something similar occurs in the future when the SQL Backup appears unresponsive or 'hanging', if possible please perform the following:

    1. Open a new query window in SSMS.
    2. Run this query: 
    USE master
GO
EXECUTE master..sqbutility 9997
GO

    3. The above makes use of the SQL Backup SQBUtility Extended Stored Procedure. This function will provide a debugging trace of the current execution process in the SQL Backup engine. Executing sqbutility 9997 will return a NULL value and generate a file in the named directory for the corresponding SQL Backup Agent (eg by default, C:Program files (x86)Red GateSQL Backup%instance%, called "SQBCoreService_bugreport.txt".

    4. From a new query window run sp_who2: 
    USE master
GO
EXECUTE sp_who2
GO

    The above will generate information on the current processes in SQL Server.

    5. Email support@red-gate.com with a copy of the "SQBCoreService_bugreport.txt" and the sp_who2 results attached. This action will create a support ticket. The engineers in the Support Team can analyse the bugreport generated and the sp_who2 results.

    6. Now restart the SQL Backup Agent Service.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • wchandlerwchandler Posts: 7
    Thank you for the reply.

    I'll get it upgraded as soon as possible and keep this incase it occurs again.

    Thanks again
    · Share on Twitter
Sign In or Register to comment.