In  a batch system it is important to log information to get a history of performance.
This makes it easy to see where bottlenecks are and to see when issues first arose.
I often log almost every statement in a stored procedure including parameters and row counts.

A note of caution. As this is used by all processes in the system do not insert within a transaction as it may block.
Gather information in a temperorary table then insert after the commit or error.
It means you will be missing information for an untrapped failure but that is better than the other possibility

The trace table should only be used for ad hoc logging from processes.
This should never be used for controlling processes.
It is for monitoring and troubleshooting.
It is common to add queries to monitor this table but it should be realised that this could fail if the source process is changed.

Trace Table
if exists (select * from sys.tables where name = 'Trace')
	drop table Trace
go
CREATE TABLE [dbo].[Trace]
	(
    [Trace_id] [int] IDENTITY(1,1) NOT NULL,
    [EventDate] [datetime] NOT NULL default getdate(),
    [Entity] [varchar](100) NOT NULL,
    [key1] [varchar](100) NULL,
    [key2] [varchar](100) NULL,
    [key3] [varchar](100) NULL,
    [data1] [varchar](max) NULL,
    [data2] [varchar](max) NULL,
    [data3] [varchar](max) NULL,
    [UserName] [varchar](128) NULL default suser_sname()
    )
go

alter table Trace add constraint pk_Trace primary key clustered
	(Trace_id)
go

Usage

create proc s_myproc (@p1 int, @p2 datetime, @p3 varchar(100))
declare @rowcount int, @inserted int, @updated int
declare @entity varchar(100)
declare @key1 varchar(100)

begin try
	select @entity = OBJECT_NAME(@@PROCID)
	select @key1 =    '<@p1=' + coalesce(convert(varchar(20),@p1),'null') + '>'
			+ '<@p2=' + coalesce(convert(varchar(19),@p2,126),'null') + '>'
			+ '<@p3=' + coalesce(@p2,'null') + '>'
	insert trace (entity, key1, key2)
	select @entity, @key1, 'start'

	update mytbl ....
	select @updated = @@rowcount

	insert trace (entity, key1, key2, data1)
	select @entity, @key1, 'update mytbl', 'updated=' = coalesce(convert(varchar(20),@updated),'null')

	insert trace (entity, key1, key2)
	select @entity, @key1, 'end'
end try
begin catch
declare @ErrDesc varchar(max)
declare @ErrProc varchar(128)
declare @ErrLine varchar(20)

	select 	@ErrProc = ERROR_PROCEDURE() ,
		@ErrLine = ERROR_LINE() ,
		@ErrDesc = ERROR_MESSAGE()
	select	@ErrProc = coalesce(@ErrProc,'') ,
		@ErrLine = coalesce(@ErrLine,'') ,
		@ErrDesc = coalesce(@ErrDesc,'')
	
	insert	Trace (Entity, key1, data1, data2)
	select	Entity = @entity, key1 = 'Failure',
		data1 = '<ErrProc=' + @ErrProc + '>'
			+ '<ErrLine=' + @ErrLine + '>'
			+ '<ErrDesc=' + @ErrDesc + '>',
		data2 = @key1
	raiserror('Failed %s', 16, -1, @ErrDesc)
end catch