Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_import_errorlog Expand / Collapse
Author
Message
Posted Thursday, August 21, 2008 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:47 PM
Points: 2, Visits: 3
Comments posted to this topic are about the item sp_import_errorlog
Post #556378
Posted Thursday, August 21, 2008 4:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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 but most of the time this is me
Post #556388
Posted Thursday, August 21, 2008 4:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,300, Visits: 1,378
Nice examples ...


Post #556399
Posted Thursday, August 21, 2008 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 14, 2013 9:46 PM
Points: 4, Visits: 25
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!

:)
Post #556580
Posted Thursday, August 21, 2008 8:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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 but most of the time this is me
Post #556593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse