Error tracking and visibility can greatly minimize disruptions and enhance client satisfaction. A simple, yet very good practice is to add error catching in stored procedures.
SQL offers many functions in the scope of the catch block, that can return information about the error. Some examples are ERROR_PROCEDURE() and ERROR_MESSAGE(), which return the name of the stored procedure and the error message respectively. You can read more about them here: https://msdn.microsoft.com/en-za/library/ms175976.aspx
The below example shows you what a stored procedure should look like that will catch and save error information:
CREATE proc dbo.sp_NameOfStoreProc
/* Queries */
/* Any errors will cause the catch section to execute */
/* Save the information about the error */
INSERT INTO dbo.ErrorLog VALUES (GETDATE(), ERROR_PROCEDURE(), ERROR_MESSAGE());
The table ErrorLog needs to be created by us. In this example, the error log table has columns for time of error, location of the error, and description of the error. You should add columns for any extra information that will help you troubleshoot.
Any time an error is encountered, it will be saved in the ErrorLog table. We can now also add mail notifications or views of the table to enhance error visibility.