SQL Job Hung. When destination had insufficient disk space
wchandler
Posts: 7
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:
Has anyone had this before? Is there a way to prevent this from occurring again?
Here is the SQL for the Job:
Thanks in advance.
Wayne.
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.
Comments
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:
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:
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
I'll get it upgraded as soon as possible and keep this incase it occurs again.
Thanks again