June 5, 2010 at 5:56 pm
Is having a table to log errors a mainstay in most database application design? I am imagining a single stored procedure used in the TRY CATCH block of every stored procedure to log all error messages to one table.
June 6, 2010 at 8:15 am
Do you mean... application side errors?
That's a good idea and some of the better designed apps I've bumped into include such a centralized log resource. Be aware this is a project on itself, the more you dig into it the deeper it gets. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 6, 2010 at 10:23 am
PaulB-TheOneAndOnly
That's a good idea and some of the better designed apps I've bumped into include such a centralized log resource. Be aware this is a project on itself, the more you dig into it the deeper it gets.
As PaulB has stated, think it out, you would must likely want to know the name of the application (App_Name), and the CURRENT_USER so as to able to follow up and learn the reason why the error occurred ....
June 6, 2010 at 6:12 pm
To give you an idea of the size to which your work can grow. Here is a table used by one company (author's name is unknown):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ErrorLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()),
[UserName] [sysname] NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) NOT NULL,
CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorLogID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for ErrorLog records.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLogID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorTime'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The date and time at which the error occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorTime'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'UserName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The user who executed the batch in which the error occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'UserName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorNumber'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The error number of the error that occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorNumber'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorSeverity'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The severity of the error that occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorSeverity'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorState'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The state number of the error that occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorState'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorProcedure'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the stored procedure or trigger where the error occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorProcedure'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorLine'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The line number at which the error occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorLine'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'COLUMN',N'ErrorMessage'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The message text of the error that occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'COLUMN',@level2name=N'ErrorMessage'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Audit table tracking errors in the the CloneOfAW database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'ErrorLog', N'CONSTRAINT',N'PK_ErrorLog_ErrorLogID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ErrorLog', @level2type=N'CONSTRAINT',@level2name=N'PK_ErrorLog_ErrorLogID'
June 6, 2010 at 10:48 pm
Thanks for the suggestions and the log table gives me some good ideas on what I could use. The application I am working with has almost no user interaction in terms of data loading. So if data is not inserted due to any kind of data-related problem (I'm thinking specifically of table constraints), nobody will know about it. I was considering an error log table that could be used for some form of daily / weekly reporting but it's not something I've ever read about in any book or seen discussed in any blog so I was wondering if it's even done in SQL Server.
June 8, 2010 at 7:12 am
See the article I posted on this site with an example of using try-catch and an error table.
June 9, 2010 at 9:35 am
That's a great article and I think it's something we'll have to implement. Thanks very much...
June 10, 2010 at 11:01 am
8kb (6/5/2010)
Is having a table to log errors a mainstay in most database application design? I am imagining a single stored procedure used in the TRY CATCH block of every stored procedure to log all error messages to one table.
I ended up doing something like this myself and made it a "standard" for new web apps that I build. The way that I actually do it is that I created a function that I ended up compiling it into a DLL (w/ other frequently used functions) which I call in the TRY CATCH blocks.
However, I took it a little further and made the error logging a little more redundant than it probably needs to be. :hehe:
In the "LogError" function, it first tries to write the application error to SQL. If, for whatever reason, it encounters an error trying to write to SQL it will write the original (application) error to a log file (e.g. ApplicationError_mm-dd-yyyy.log) AND write the new (SQL) error to a separate log file (e.g. DatabaseError_mm-dd-yyyy.log).
The only shortfall for that function I created is that I didn't include any code to notify me if a database error even occurred. So I have to manually (if I remember to) look for any physical log files. :ermm:
Well, that's my two cents on the topic. 🙂
June 10, 2010 at 11:51 am
Something just occurred to me. If the outside application
is managing a BEGIN and COMMIT TRAN in the c# code, then doesn't that mean that any errors logged to the table
would be rolled back if the transanction was rolled back in c#?
June 10, 2010 at 12:38 pm
I believe so. Although, I'm not too familiar with the COMMIT ROLLBACK options (sadly) because I've just barely started focusing on learning more about SQL this year. I've only known basic SELECT, UPDATE, and INSERT statements for the longest time. *SIGH* Haha. But my guess would be that it'll have the same results as if you had issued the command in T-SQL.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply