SqlLogger Class

-- This script creates a sample database, table and procedure to be used by the SqlLogger
-- Drop the database if it exists
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MSBuildLogs')
BEGIN
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MSBuildLogs'
    USE [master]
    ALTER DATABASE [MSBuildLogs] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE [MSBuildLogs] SET SINGLE_USER 
    DROP DATABASE [MSBuildLogs]
END
-- Create the database. Alter the paths as necessary to suite your environment
CREATE DATABASE [MSBuildLogs] ON PRIMARY 
( NAME = N'MSBuildLogs', FILENAME = N'C:\a\MSBuildLogs.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'MSBuildLogs_log', FILENAME = N'C:\a\MSBuildLogs_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Create the table
USE [MSBuildLogs]
GO
CREATE TABLE [MSBuildLogs].[dbo].[BuildLogs](
    [id]        [int]            IDENTITY(1,1) NOT NULL,
    [BuildId]    [int]            NULL,
    [BuildName]    [nvarchar](100)    NULL,
    [Event]        [nvarchar](50)    NOT NULL,
    [Message]    [nvarchar](1500) NULL,
    [EventTime]    [datetime]        NOT NULL
) ON [PRIMARY]
GO
-- Create a clustered index on the colums this table is likely to be searched on
CREATE CLUSTERED INDEX [cidx_BuildId,EventTime] ON [MSBuildLogs].[dbo].[BuildLogs] 
(
[BuildId] ASC,
[EventTime] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Create the stored procedure
CREATE PROCEDURE [dbo].[msbep_SqlLogger]
    @BuildId    int = NULL,
    @BuildName    nvarchar(50) = NULL,
    @Event        nvarchar(50),
    @Message    nvarchar(1000) = NULL,
    @ClearLog    bit = 0
AS
BEGIN
DECLARE @EventTime DATETIME;
SET @EventTime = GETDATE();
IF ( @ClearLog = 1)
BEGIN
    DELETE FROM [dbo].BuildLogs
    WHERE BuildId = @BuildId
END
INSERT INTO [dbo].BuildLogs (BuildId, BuildName, [Event], [Message], EventTime)
VALUES    (@BuildId, @BuildName, @Event, @Message, @EventTime)
END