sp_import_errorlog

  • anand mahendra

    Grasshopper

    Points: 14

    Comments posted to this topic are about the item sp_import_errorlog

  • Johan Bijnens

    SSC Guru

    Points: 134309

    Nice proc.

    In SQL2005 access to the errorlog are restriced.

    Because some of my developers "realy" needed errorlog info, I've come up with this alternative.

    I just import the errorlog using a sqlagent job on a regular bases (1h)

    using this proc.

    /*

    * ALZDBA dd 20070605

    * Because sp_readerrorlog needs securityadmin authority to be run, we've created this alternative

    */

    use Master

    go

    /*

    drop table dbo.T_DBA_SQLServerErrorlog

    */

    if object_id('dbo.T_DBA_SQLServerErrorlog') is null

    begin

    create table T_DBA_SQLServerErrorlog (

    RowNumber int identity(1,1) primary key not null,

    LogDate datetime not null,

    ProcessInfo sysname not null,

    [Text] varchar(max),

    DtTableRefresh datetime not null default getdate()

    )

    -- grant select on dbo.T_DBA_SQLServerErrorlog to [Server_Dev_group]

    go

    end

    go

    if object_id('spc_DBA_RefreshErrorlogData') is not null

    begin

    drop procedure spc_DBA_RefreshErrorlogData

    end

    go

    Create proc spc_DBA_RefreshErrorlogData

    as

    begin

    set nocount on

    /*

    * clear old data

    */

    truncate table dbo.T_DBA_SQLServerErrorlog

    /*

    * load new data

    */

    insert into dbo.T_DBA_SQLServerErrorlog (LogDate, ProcessInfo, [Text])

    exec sys.sp_readerrorlog

    end

    GO

    if object_id('sp_DBA_ErrorlogData') is not null

    begin

    drop procedure sp_DBA_ErrorlogData

    end

    go

    Create proc sp_DBA_ErrorlogData

    as

    begin

    select *

    from dbo.T_DBA_SQLServerErrorlog

    end

    go

    /*

    * Schedule job to refresh data hourly

    */

    USE [msdb]

    GO

    /****** Object: Job [DBA_RefreshErrorlogData] Script Date: 06/05/2007 14:02:57 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [Database Maintenance] Script Date: 06/05/2007 14:02:58 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_RefreshErrorlogData',

    @enabled=1,

    @notify_level_eventlog=2,

    @notify_level_email=0,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'Database Maintenance',

    @owner_login_name=N'sa', @job_id = @jobId OUTPUT

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    /****** Object: Step [spc_DBA_RefreshErrorlogData] Script Date: 06/05/2007 14:02:58 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'spc_DBA_RefreshErrorlogData',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'EXEC dbo.spc_DBA_RefreshErrorlogData',

    @database_name=N'master',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Dagelijks_Ieder_Kwartier',

    @enabled=1,

    @freq_type=4,

    @freq_interval=1,

    @freq_subday_type=4,

    @freq_subday_interval=15,

    @freq_relative_interval=0,

    @freq_recurrence_factor=0,

    @active_start_date=20070605,

    @active_end_date=99991231,

    @active_start_time=2,

    @active_end_time=235959

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice examples ...

  • juanfcoy

    Grasshopper

    Points: 20

    Good tool to have! Thanks for posting.

    Although, a tool I have been using to read/query SQL Logs and all other relevant OS logs is Microsoft's Log Parser 2.x. That is a great tool that will spare you from having to import the log into a table in order to query.

    Cheers!

    🙂

  • Johan Bijnens

    SSC Guru

    Points: 134309

    juanfcoy (8/21/2008)


    ...Although, a tool I have been using to read/query SQL Logs and all other relevant OS logs is Microsoft's Log Parser 2.x. ....

    Indeed, but some people prefer using the "known software"s tools 😉

    We mainly use Log Parser to import e.g. print server event logs into a sqlserver table because that is a huge volume of date.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply