Getting SQL Server Error Messages to Write to the Event Log

SQL Server alerts can give you valuable insight into issues with your servers and can track a multitude of different errors, messages and events. To view all the built-in system error messages, run the following script on Master database.

SELECT *

  FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

1033 is the English language ID taken from the syslanguages table

SELECT MSGLANGID

  FROM SYSLANGUAGES

 WHERE NAME = 'US_ENGLISH'

In SQL Sever 2008 there are over 8900 different messages that can be returned from the server, all with varying severities and text messages. The event I’m going to focus on here is message_id 229, “The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'”.

SELECT * FROM SYS.MESSAGES

 WHERE LANGUAGE_ID = 1033

   and message_id = 229

Message_id	Language_id	Severity	Is_event_logged	Text
229	1033	14	0	The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

If I want to be alerted any time a user tries to call a stored procedure or query a table they dont have access to, I can create a SQL Agent Alert using the following procedure and further configuring the alert to email me.

EXEC msdb.dbo.sp_add_alert @name=N'Insufficient Permission Error',

            @message_id=229,

            @severity=0,

            @enabled=1,

            @delay_between_responses=0,

            @include_event_description_in=1,

            @category_name=N'[Uncategorized]',

            @job_id=N'00000000-0000-0000-0000-000000000000'

GO

After this is setup, you would expect that everytime this error is generated an email would be sent to you, but…… going back to the IS_EVENT_LOGGED column in the SYS.MESSAGES table, you see that this event is not logged and will not be sent to the Event Log.

So how do I get this error to log to the Event Log and then sent in an email? In SQL 2005 SP2+ and SQL 2008, the following command can be issued to modify the existing messages:

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'True'

Setting the parameter ‘With_Log’ to a value of true tells SQL that the triggering of this event will always result in the error being written to the Event Log

NOTE: If/when you decide that you no longer want to monitor this event, just disabling the alert will not be enough to keep the event from being logged to the Event Log. You must run the following script to keep it from being logged.

EXEC sp_altermessage

 @message_id = 229

,@parameter = 'WITH_LOG'

,@parameter_value = 'False'

Validating Row Counts in Transactional Replication

One part of administering replication should be validating that the articles being replicated are actually making it to the subscriber. There have been many times that replication has had no errors and the agents seem to be running without issue only to find out that the data on the subscriber is stale.

Luckily, there are some stored procedures that will do most of the leg work for you. In particular, exec sp_publication_validation @publication = ‘<publication name>’ which can only be used on transactional replication. According to Books Online, the procedure calculates the rowcount or checksum on each of the articles at the Publisher, posts the results to the distribution database, and the next time the distribution agent runs it checks those rowcounts against the subscriber.

After this procedure is ran, you can look inside the replication monitor to check the results.

If you would like to be automatically notified if an article fails the data validation, a SQL agent alert called “Replication: Subscriber has failed data validation” can be enabled to look for alert_error_code 20574 in the MSDB..SysReplicationAlerts table. This alert should have been creates when replication was configured. Alternatively, you could set up a SQL Agent job to look for these alert codes and email you.

For my environment, I’ve setup the sp_publication_validation procedure to run every two hours and enabled the “Replication: Subscriber has failed data validation” alert (having it email me if it is triggered).

How to quickly disable multiple SQL Agent jobs on the server.

If you have ever needed to quickly disable a group of SQL Agent jobs without turning the agent off totally, there is a stored procedure out there that will allow you to turn the jobs off programmatically. If you have your agent jobs categorized, and only want to turn off a certain category (application processes, for instance) but leave replication jobs running, you can run the following script to find the enabled jobs that are in that category:

SELECT SJ.Name, SJ.[Enabled], SC.Name

  FROM msdb..sysjobs SJ inner join msdb..syscategories SC

    on SJ.category_id = SC.category_id

 Where SJ.[Enabled] = 1

   and SC.Name = '<CategoryName>'

Once you’ve reviewed the jobs to be disabled, run the following script to generate the code needed to disable all the jobs in the category. Since you will be running the output of the following script, you will have the ability to control which jobs are disabled.

Select 'Exec MSDB.dbo.sp_update_job @job_name = N''' + SJ.Name + ''', @Enabled = 0'

  FROM msdb..sysjobs SJ inner join msdb..syscategories SC

    on SJ.category_id = SC.category_id

 Where SJ.[Enabled] = 1

   and SC.Name = '<CategoryName>'

When was a SQL Agent job was created or when was the last time it was modified?

If you ever need to know the date a SQL Agent job was created or the last time it was modified, you can find the information in the sysjobs table in the MSDB database.

Select Name,

       Date_Created,

       Date_Modified

  From msdb..sysjobs