Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When issuing a backup command within a stored procedure that uses a try catch and dynamic sql, the error messages are very general when compared to running the backup command directly.

Try/Catch within SP:

    begin try
        execute sp_executesql @sql;  -- a backup command
    end try
    begin catch  
        print ERROR_MESSAGE();  -- save to log, etc.
    end catch

Results in

50000:usp_Backup:117: BACKUP DATABASE is terminating abnormally.

wheareas issuing the raw command:

    backup DATABASE someDb to disk...

Results in better details:

Lookup Error - SQL Server Database Error: A nonrecoverable I/O error occurred on file "H:\FolderName\Filename.bak:" 112(There is not enough space on the disk.).

Is there a way to catch these details into variables within the stored procedure (to log, pass back to caller, for retry logic)? It seems the details are coming through on the message channel but I would like them available within the SP.

share|improve this question
    
You may want to see this: stackoverflow.com/questions/5966670/… –  8kb Jun 26 '12 at 0:47

3 Answers 3

up vote 4 down vote accepted

When BACKUP DATABASE generates an error, it actually generates two. Unfortunately TRY/CATCH is not capable of capturing the first error; it only captures the second error.

I suspect your best bet to capture the real reason behind a failed backup is to automate your backups through SQLCMD (with -o to send output to a file), SSIS, C#, PowerShell etc. All of which will have much greater control over capturing all of the errors.

The SO answer in the comment suggests using DBCC OUTPUTBUFFER - while it's possible, this does not seem like child's play at all. But you can have fun with this procedure from Erland Sommarskog's site. But this still doesn't seem to work well in combination with TRY/CATCH.

The only way I seemed to be able to capture the error message with spGET_LastErrorMessage is if the actual error does get thrown. If you wrap it in a TRY/CATCH the error gets swallowed and the stored procedure does nothing:

BEGIN TRY
  EXEC sp_executesql N'backup that fails...';
END TRY
BEGIN CATCH
  EXEC dbo.spGet_LastErrorMessage;
END CATCH

In SQL Server < 2012 you can't re-raise the error yourself, but you can in SQL Server 2012. So these two variations work:

CREATE PROCEDURE dbo.dothebackup
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sp_executesql N'backup that fails...';
END
GO

EXEC dbo.dothebackup;
EXEC dbo.spGET_LastErrorMessage;

Or in 2012 only, this works, but to a large degree defeats the purpose of TRY/CATCH, since the original error still gets thrown:

CREATE PROCEDURE dbo.dothebackup2
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN TRY
    EXEC sp_executesql N'backup that fails...';
  END TRY
  BEGIN CATCH
    THROW;
  END CATCH
END
GO

EXEC dbo.dothebackup2;
EXEC dbo.spGET_LastErrorMessage;

In both of these cases the error is still thrown to the client, of course. So if you're using TRY/CATCH to avoid that, unless there is some loophole I'm not thinking of, I'm afraid you'll have to make a choice... either give the user the error and be able to capture details about it, or suppress both the error and the actual reason.

share|improve this answer
    
As ridiculous as it is, the Sommarskog approach doesn't seem out of the question if I just wanted to provide some context to the caller within an interface. Better than starting a separate process. Are you saying it won't work within a TRY/CATCH? –  crokusek Jun 26 '12 at 4:39
    
@crokusek I tried one variation and the result came up empty. I'll give it another shot today. –  Aaron Bertrand Jun 26 '12 at 11:23
    
In the case of no error, does LastErrorMessage() get the results of any previous error from the session? Then if the last two exec's are run as script, perhaps the first exec could be wrapped within a try/catch and within the catch, set a variable, then re-throw. Then the LastError is called only if the variable is set. Assumes the re-throw doesn't skip the 2nd call which I think is usually true within a scripting context. I still may not be able to use this approach in the end because it can't all be placed within a SP if I understand correctly. Thanks though! –  crokusek Jun 26 '12 at 22:44

You can log the error details to a table. You could also create a log file, but that may require a CLR or xp_cmdshell to do. You can also send database mail, but that may cause spam issues and is not a proper log.

The table is simplest.

  1. Create a table for storing the errors
  2. Create a stored procedure that inserts into the error table
  3. Call the stored procedure in the catch block

Look over Jeremy Kadlec's example provided in the link below:

http://www.mssqltips.com/sqlservertip/1152/standardized-sql-server-error-handling-and-centralized-logging/

share|improve this answer
1  
The problem is not about what to do with the errors, it's that the proper error message is not available for some commands within CATCH. This is because only the last error message is returned in ERROR_MESSAGE()... –  Aaron Bertrand Jun 26 '12 at 1:17

Well I know this is an old thread, and I know what I'm about to propose is a convoluted hack, but just in case it can help anyone, here goes: Since these backup errors get logged, you can use xp_readerrorlog in the catch block to scrape the log for related message (error or info). You can google around for xp_readerrorlog params but in short you can spec a search-string and a begin time filter which are useful in this case. Not sure if this would help your retry logic, but to capture either info or errors for logging, I came up with something like this...

IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (LogDate datetime,ProcessInfo nvarchar(100),LogText nvarchar(4000))
BEGIN TRY
SELECT @begintime = GETDATE()
EXEC sp_executesql @SQL --your backup statement string
INSERT #Results
EXEC  xp_readerrorlog 0, 1, N'backed up',@databasename,@begintime
SELECT @result = LogText from #Results where ProcessInfo = 'Backup' order by logdate desc
END TRY
BEGIN CATCH
INSERT #Results
EXEC  xp_readerrorlog 0, 1, N'Backup',@databasename,@begintime
SELECT @result = LogText from #Results where ProcessInfo = 'spid'+cast(@@SPID as varchar(6)) order by logdate desc
END CATCH
PRINT @result

HTH

share|improve this answer

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.