Sql Server Procedure to Write Log into a File

Sometimes you have to write a long running time procedure and you want to log to file what is happening during the execution, in order to check the status, or just for event logging purpose. Here I propose a simple stored procedure for Sql Server that allows you to do that, using it instead of the classic "print" to dbms console.

Stored Procedure

Here is the stored procedure that do the work. Just copy and execute the code to create it:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WRITELOG] @loglevel nvarchar(30), @logcontext nvarchar(30), @logmessage nvarchar(1000) 
AS
BEGIN
    SET NOCOUNT ON;
    declare
    @base_path as nvarchar(1000) = 'C:\sqlserver_logs\',
    @filename as nvarchar(1000) = 'mylog',
    @currdate as nvarchar(24),    
    @shortdate as nvarchar(24),
    @cmdtxt as nvarchar(255)
    
    select @currdate = CONVERT(NVARCHAR(24),GETDATE(),120);
    select @shortdate = CONVERT(NVARCHAR(24),GETDATE(),112);

  
  select @cmdtxt = 'echo ' + @currdate + ' - [' + @loglevel + '] ' + 
@logcontext + ' - ' + @logmessage +' >> ' + @base_path + @filename
 + '_' + @shortdate + '.log';
    exec master..xp_cmdshell @cmdtxt    
END

You can change the directory in which you want to store logs, in this example is C:\sqlserver_logs\ (please don't forget the slash "/" or backslash "\" at the end of the path!!) and the base file name, in this case "mylog". The procedure will create in that directory the log files in the format "filename_YYYYMMDD.log"

 

Enable xp_cmdshell

Before using it you have to enable xp_cmdshell, to do that execute the following query:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Usage

You can now use this procedure in your own script as in the following example:

declare @logtext varchar(1000) = 'This is my log text';

EXEC WRITELOG @loglevel = 'INFO', @logcontext = 'MYCONTEXT', @logmessage = @logtext;

So you can specify a log level (i.e. INFO, WARN, ERROR, DEBUG) a context (i.e. the procedure name or something else) and the log text.

Output

The output is a file (or more if the scripts takes 2 or more days) placed in the specified directory, with the specified name and the execution day, i.e.:

mylog_20150707.log

The content in the file will be something like:

2015-07-07 16:05:11 - [INFO] MYCONTEXT - This is my log text