Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Display All Errors Fired in a Restore Procedure

By Brian Awe,

I’ve written a stored procedure that refreshes the data of any test and development environment from our production environment; the stored procedure runs under a login which has sufficient permissions to complete the task so that any user with execute permissions on the stored procedure can refresh their databases from the production databases. One of the issues I encountered in developing this was displaying to the user running the stored procedure all of the errors returned by the restore statement; this is because when the SQL Server error handling system functions, such as ERROR_NUMBER(), ERROR_MESSAGE(), etc are used to retrieve and display the errors to the user running the stored procedure, only the last error is displayed to the user.

As an example if a user tried to restore a database using a stored procedure such as the one created in OriginalRestoreProc.sql but there was not enough available space to complete the restore; the only error displayed to the user is:

Error 3013: Restore was terminated abnormally.

This means that the user has no immediate way of telling why the restore failed, and the error itself is not in any way user friendly.

In order to capture all of the errors to return to the user I set up an extended event session to capture the restore errors, please note that extended events were introduced in SQL Server 2008 onwards, so this technique will not work in SQL Server 2005 or less.

The extended events engine for SQL Server was introduced as a lightweight replacement for SQL Trace, an event session is setup to watch for a given event, and when it occurs, to capture the data configured by the event session and sending this data to a given target, for an in-depth introduction to extended events see technet article http://technet.microsoft.com/en-us/library/bb630354(v=sql.100).aspx .

Listing RestoreErrors_XEvent.sql creates the extended events session that looks for the errors generated by restore statements called by the stored procedure login account, specifically the errors listed in the table below.

To get the captured extended events returned to the user, the original stored procedure was modified, by adding additional code to the catch block; this code reads the extended event data written to the target of the extended event session which is in XML format, and then converts it to a tabular format and returns the results to the user, see listing ModifiedCatchBlock.sql, as mentioned earlier the Catch block contains the code, called when a SQL Server script encounters an error if a Try-Catch construct is used for error handling.

So using the scenario mentioned earlier where a user tries to restore a database but there is not enough space to complete the restore, the image below shows the additional results that are now available to the user via the results tab in SQL Server Management Studio. Note that in this scenario where there is not enough space to complete a database restore two errors are raised. The first error to show the reason why the restore failed and how to correct it. The second shows the phase of the restore that the error occurred in, namely the planning phase. 

You will not always see two errors returned. The number of errors returned vary depending on the initial raised error. For example, if a user tried to restore a database, that their current session was connected to, only one error would be returned. That would be: Error: 3102, %ls cannot process database '%ls' because it is in use by this session. It is recommended that the master database be used when performing this operation.

Conclusion

Using the power of Extended Events, I was able to get report all errors raised within a stored procedure and display them to a user. This is particularly useful in SQL Server 2008R2 where there are extended events but no THROW error handling command.

To read more about extended events, I recommend that you check out Jonathan Kehayias' excellent series on the subject http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/an-xevent-a-day-1-31-an-overview-of-extended-events.aspx

Resources:

OriginalRestoreProc.sql | RestoreErrors_XEvent.sql | ModifiedCatchBlock.sql
Total article views: 2981 | Views in the last 30 days: 45
 
Related Articles
FORUM

Extended Procedure

Extended Procedure

BLOG

Error 25602 When Creating an Extended Events Session: a Security Risk

 Error 25602 When Creating an Extended Events Session: a Security Risk I came across an interesting...

FORUM

Extended stored procedure

Extended stored procedure

FORUM

extended stored procedure

extended stored procedure

BLOG

Basics QA on Extended Event

Basics QA on Extended Event     I am learning Extended Event so this is my first blog on extend...

Tags
administration    
extended events    
restore    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones