In cases where you find that running SQL Backup via the extended stored procedure seems to have hanged, there are a couple of things you can do to help Red Gate Support in identifying and addressing your issues faster.
In most cases, the SQL Backup process has either encountered a critical error, or is taking an extremely long time performing a specific task.
When SQL Backup encounters a critical error, it generates a stack trace to a log file named SQBCoreService_<instance name>_bugreport.txt. So for a local instance, the file is named SQBCoreService_(LOCAL)_bugreport.txt, and for a instance named SQL2008, the file is named SQBCoreService_SQL2008_bugreport.txt.
In SQL Backup versions 5 and earlier, this file is generated in the folder where the SQL Backup Agent service (SQBCoreService.exe) is installed. In version 6 and newer, the file is generated in the '<system drive>\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log' folder. On Windows Vista and newer operating systems, the file can be found in the '<system drive>:\ProgramData\Red Gate\SQL Backup\Log' folder.
If you do not find such a file in that folder, there may be 2 possibilities:
·SQL Backup has never encountered any critical errors since it was installed
·The SQL Backup Agent service startup account has no rights to create that file in that folder. To verify this, run the following and check if the file was generated:
If the file was not generated, you would need to grant the necessary rights to the SQL Backup Agent service startup account to create files in that folder.
Abnormal execution time
If SQL Backup seems to be taking too long to perform a task, and you do not find the log files described above, you can run the following code:
to generate a stack trace of the SQL Backup process. The stack trace is saved to the log file described above, and is invaluable to the support team in identifying what exactly is happenning in SQL Backup. However, when generating the stack trace, please ensure that there are as little unnecessary tasks running within SQL Backup as possible. Also note that the extended stored procedure will just return a NULL value.
E.g. if you backup hangs, first ensure that no SQL Backup GUI is connected to the server. Then check that no other SQL Backup backups or restores are running. Only then should you generate the stack trace. This is to minimise the 'noise' in the trace file.
When you then contact Red Gate Support with your issue, you should provide the following:
·a description of the issue you are facing
·the log file containing the stack trace
·the SQL Backup command that was ran at the time SQL Backup became responsive