August 21, 2008 at 4:09 am
Comments posted to this topic are about the item sp_import_errorlog
August 21, 2008 at 4:22 am
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 but most of the time this is me
August 21, 2008 at 4:50 am
Nice examples ...
August 21, 2008 at 7:52 am
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!
🙂
August 21, 2008 at 8:04 am
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 but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply