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 «««45678»»»

How to centralize your SQL Server Event Logs. Expand / Collapse
Author
Message
Posted Friday, May 25, 2012 9:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 9, 2013 12:32 PM
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
Post #1306654
Posted Friday, May 25, 2012 9:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:00 PM
Points: 514, Visits: 1,732
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
Post #1306657
Posted Friday, May 25, 2012 9:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:00 PM
Points: 514, Visits: 1,732
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 ?
Post #1306660
Posted Friday, May 25, 2012 10:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 9, 2013 12:32 PM
Points: 12, Visits: 34
Thanks for your quick response. No, I don't have a FILEGROUP
Post #1306697
Posted Friday, May 25, 2012 10:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 9, 2013 12:32 PM
Points: 12, Visits: 34
How do I create a filegroup?

Thanks,
Marcelene
Post #1306704
Posted Friday, May 25, 2012 11:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:39 PM
Points: 3, Visits: 62
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?
Post #1306744
Posted Monday, May 28, 2012 4:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:24 AM
Points: 6, Visits: 74
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???
Post #1307224
Posted Monday, May 28, 2012 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 10:11 PM
Points: 1, Visits: 121
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
Post #1307388
Posted Wednesday, May 30, 2012 3:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 2:02 AM
Points: 4, Visits: 103
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
Post #1308196
Posted Wednesday, May 30, 2012 4:24 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:00 PM
Points: 514, Visits: 1,732
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
Post #1308226
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse