SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to centralize your SQL Server Event Logs.


How to centralize your SQL Server Event Logs.

Author
Message
msilver
msilver
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 34
I'm using SQL Server 2008 R2. I created the database successfully but when I run the next script, I get an error (Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'PRIMARY'. What am I doing wrong?

Thanks,
Marcelene Silver
Geoff A
Geoff A
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2502 Visits: 1808
bev.bellisio (5/25/2012)
I was successful setting this up on the server I wanted to use as Central server. However, when I tried to change one of my other servers' Agent properties to forward events to this server, it doesn't work. I get messages on the remote server's Event log - Unable to open the eventlog on forwarding server 'xxxxx' (reason: Access is denied). (Central server name has been masked).

What permissions do I need to give on the Central server to allow the other servers to do this forwarding?

Thanks!
Bev Bellisio


make sure that you have enabled the Distributed Transacation Coordinator on the 'sending' server.
Under Security Settings you have to Allow Network DTC Access
Geoff A
Geoff A
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2502 Visits: 1808
msilver (5/25/2012)
I'm using SQL Server 2008 R2. I created the database successfully but when I run the next script, I get an error (Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'PRIMARY'. What am I doing wrong?

Thanks,
Marcelene Silver


Do you have a FileGroup named PRIMARY ?
msilver
msilver
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 34
Thanks for your quick response. No, I don't have a FILEGROUP
msilver
msilver
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 34
How do I create a filegroup?

Thanks,
Marcelene
bev.bellisio
bev.bellisio
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 68
sorry, having trouble quoting the post I wanted to - mine is a few panes back, re the authentication problems. I enabled DTC on the 'sending' server, but it still cannot post to the event log of the Central server. Do I also need to enable DTC on the Central server?
perfectillusion29
perfectillusion29
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 77
Quick question for all gurus....i want to run the job once a day instead of running all the time and dump all the event logs into staging table...any suggestions???
faisalsaeedmk
faisalsaeedmk
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 155
hi,
this is really a great thing for logging. i have also used the script for this purpose and succesfully runing the code. but i wana one change. this code enter the value into the db table when events trigger. i want that this code should run once in the day at a specifice time and log the whole information. i tried serveral thing but didnt get any thing,

i made this change in the query

query = "Select * from __InstanceCreationEvent" _
& " " & "where TargetInstance isa 'Win32_NTLogEvent'" _
& " " & "and TargetInstance.Logfile = 'Application'" _
& " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" _
& " " & "and (TargetInstance.SourceName like 'MSSQL%')"_
& " " & "and (TargetInstance.timewritten = (now))" --- serveral more option also tried without any success

Faisal DBA





please any one has any solution on this
phildale
phildale
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 110
Hi Geoff,

Thanks for the articles, they've been a great use and were easy to follow.

I've followed through the tutorial and the EventLogStaging table is written to when using the RAISERROR example that you mentioned.

However, I want to use this for all SQL Server errors, and I've noticed that other Errors which end up in the Event Viewer Application log are not being picked up by the VB script. I'm not sure why, as to my knowledge the 'where' in the VB script should not exclude them.

Here is an example of an error which exists in the Event Viewer Application log but hasn't been picked up by the script:

Log Name: Application
Source: SQLISPackage100
EventID: 12291
Level: Error
User: DOMAIN\SQLServiceAccount
OpCode:
Task Category: None
Keywords: Classic
Computer: SQLSERVER.DOMAIN.LOCAL
Message: Package "Package1" failed.

And here is the test one which you used in the article, which is being picked up by the script:

Log Name: Application
Source: MSSQLSERVER
EventID: 17063
Level: Error
User: DOMAIN\myuseraccount
OpCode:
Task Category: Server
Keywords: Classic
Computer: SQLSERVER.DOMAIN.LOCAL
Message: Error: 50000 Severity: 16 State: 1 working great

This is the select which I am using (I've removed the 'like 'MSSQL%'' from the where):

query = "Select * from __InstanceCreationEvent" _
& " " & "where TargetInstance isa 'Win32_NTLogEvent' and" _
& " " & "(TargetInstance.Logfile = 'Application' or TargetInstance.Logfile = 'System')" _
& " " & "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)"

Any ideas why other types of errors (which aren't raised using RAISERROR) aren't being picked up by the script?

Any help would be appreciated.

Thanks

Phil
Geoff A
Geoff A
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2502 Visits: 1808
Hi Phil,

Only MSSQL logs will be forwarded to the central server. That is a SQL Server Agent function.
SQL Server Agent will not forward non SQL Application log entries.

The modification to the script will work, but only on the server where the error occurs.

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search