Force sql serve to write to eventlog

Posted on 2011-10-07
Last Modified: 2012-05-12

The list of all SQL Server errors:

SELECT * FROM sysmessages where msglangid = 1033

I want to force SQL Server to write all errors with a servery higher than X to the operating system event viewer because it's the only way I have to capture sql server errors with my office monitoring tool.

How to do it ?
Question by:migav
    LVL 39

    Expert Comment

    You could set a  New SQL Alert under SQL Agent in SSMS that can do that for you.

    Author Comment

    Help, I don't find how to write to event viwer ..
    LVL 39

    Accepted Solution

    My bad...I should have included more detail:

    You can add a new alert as mentioned and under the response you can execute a job instead of sending notification and in that job you can use xp_logevent  stored proc to do what you need:
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Perhaps I am being dense but if you want to write to the Event Log, have you considered something like this:
    RAISERROR ('Houston we have a problem!', 10, 1) WITH LOG
    LVL 5

    Assisted Solution

    So, what if you want to log information messages, or messages of low severity? If you wish to have an alert on any errors that are of a severity less that 19, then you have to modify their entry in the sysmessages table to set them to be always logged. You do this using sp_alterMessage with the WITH_LOG option to set the dLevel column to 128 . If a message has been altered to be WITH_LOG, it is always subsequently written to the application log, however the error happens. Even if RAISERROR is executed without the WITH LOG option, the error that you have altered is written to the application log, and is therefore spotted by the alert. There are good reasons for wanting to do this, as it will then log, and optionally alert you to, syntax errors that are normally seen only by the end-user.

      You can force any error that is triggered programmatically to be written to the error log by using the WITH LOG parameter with the RAISERROR command. So, with a user-defined error severity (9) you can log an incident, cause an alert to be fired, which in turn emails someone, or runs a job, simply by using RAISERROR. Naturally, because the job that responds to the alert can be run by the Agent under a different User, you do not need to assign unsafe permissions to the ordinary user. You can use xp_LogEvent if, as is likely, you do not want the user to see the error. (Only the Raiserror call can utilize the 'PrintF' formatting  placeholders in the error messages, so logging the error with a formatted message using xp_logevent results in a literal recording of the message, string format identifiers and all.)

    You can read your Forcibly   Logged errors by following parameters  

    Example EXEC xp_ReadErrorLog 0, 1, NULL, NULL, @DatelessHour, @CurrentDate

    Author Closing Comment

    Thanks !

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now