SQL Server: Best way to design centralize log table

Sudip Bhatt 2,276 Reputation points
2020-10-04T18:34:02.713+00:00

I am looking for a best log table design where i will save different kind of log like error, warning etc. please share the best log table structure where i will be inserting data from my store procedure.

thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,392 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,606 questions
0 comments No comments
{count} votes

Accepted answer
  1. m 4,271 Reputation points
    2020-10-05T03:37:29.2+00:00

    Hi @Sudip Bhatt ,

    Quote code from this doc. : sql-server-error-logging-and-reporting-within-a-stored-procedure

    CREATE TABLE [dbo].[ErrorLogTable]  
    (  
        [ErrorID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,  
        [ErrorNumber] [nvarchar](50) NOT NULL,  
        [ErrorDescription] [nvarchar](4000) NULL,  
        [ErrorProcedure] [nvarchar](100) NULL,  
        [ErrorState] [int] NULL,  
        [ErrorSeverity] [int] NULL,  
        [ErrorLine] [int] NULL,  
        [ErrorTime] [datetime] NULL      
    );  
      
    CREATE PROCEDURE [dbo].[ErrorLogInsert]  
    AS  
      
         INSERT INTO [ErrorLogTable]    
             (  
             ErrorNumber   
            ,ErrorDescription   
            ,ErrorProcedure   
            ,ErrorState   
            ,ErrorSeverity   
            ,ErrorLine   
            ,ErrorTime   
           )  
           VALUES  
           (  
             ERROR_NUMBER()  
            ,ERROR_MESSAGE()  
            ,ERROR_PROCEDURE()  
            ,ERROR_STATE()  
            ,ERROR_SEVERITY()  
            ,ERROR_LINE()  
            ,GETDATE()    
           );  
    

    If you want to read all the errors and warings, you can follow scrips from this one: read-all-errors-and-warnings-in-the-sql-server-error-log-for-all-versions

    More information: sql-server-error-logging-from-a-stored-procedure ,logging-sql-server-database-errors, simple-way-to-find-errors-in-sql-server-error-log

    BR,
    Mia


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 107.5K Reputation points MVP
    2020-10-04T19:18:19.187+00:00

    Show you a table without knowing you requirements? I think you are over-estimating the capabilities of our crystal balls somewhat.

    What I can say is that you should probably have an id column as a primary key and a default of NEXT VALUE FOR SomeSequenceObject. These kind of tables rarely have a natural key. You also need a column with date and time, and for this I recommend the data type datetime2(3). Particularly if the time stamps are collected within SQL Server, as you cannot get higher accuracy than a millisecond anyway.

    The rest you have to take from your requirement specification.

    1 person found this answer helpful.