In this article, we explain solutions to the SQL Server error for ‘Event ID not found’.

Apart from all its features and functions, SQL Server can also be a storehouse of confusion if you are not very familiar with all that it provides and what all it can be used for. For all the events and operations that we perform in SQL Server, we can refer to the event log for details. But keeping in mind the high-end features and some complexities of the application, there might also be times when you are completely unable to find a given Event ID from the SQL Server source.

Solve “Event ID not found” Error in SQL Server

Error

Your SQL Server application’s event log might be troubling you with an error that fails to give you the details of an Event ID from SQL Server Source. You can try searching the error log, but it might prompt you with an error message that says that password you entered was not correct, and did not match. This appearing error message might just be showing you placeholders for all you know. One of the main reasons why this happens is because a file is missing and the event viewer has been enabled for decoding the message to show just place holders.

You might get more information on the occurring error by checking the sys.messages. The correct message can still be found in event log.

Solution

Since the error message we are viewing is showing only placeholders, we know that encoding is enabled and we are seeing a decoded version of the same, but not the complete text. This decoding takes place mainly because of a file that has been stored in a registry key.

Depending on what kind of instance you are making use of, you will be able to find a path to the same. For a default instance, the path will be found in ‘EventMessageFile’. Once you are able to find the path of the instance, you will also know its location. One of the main reasons why you were not able to find it in the event log can be because the version was incorrect.  You can try copying it from any other machine and then reopen it in event viewer.

Once you perform this action, you would be able to get the complete information for the Event ID.

Important Points

As and when we start working on SQL Server there are a lot of features and functions that we are not aware of, with the time we not only begin understanding them but also learn how to make the best of them. However, as a user of the application, it is important for you to know that even the most experienced of DBAs can often come across unforeseen errors when making use of the application. By attempting to figure out solutions you would never get stuck at any of the errors you experience in the application. As at the end of the day you can always take help of a repair SQL Server tool in case you experience a more severe problem.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com

Be the first to comment
             

In this article, we look at roles being used to grant permissions on the report server. We also look at who sets these permissions how these permissions are stored.

Reporting Services in SQL Server use a role-based authorization and authentication subsystem in order to determine the users who can perform an operation or access items on a report server. Role-based system is used for categorizing authorization of different roles and action which can be performed by a user or a group. This authentication is based on custom authentication module or built-in Windows Authentication that is provided to the users. Users can custom or predefine roles using either of these authentication types.

How Permissions can be Granted in Report Service

Using Role to Grant Permissions on the Report Server

All users interact or access a report server based on the role that is defined to a specific level for a specific person or a group. Reporting Services include predefined roles which can be assigned to users or groups in order to provide them with immediate access to interact with a report server. Content Manager, Browser, and Publisher are some common examples of these predefined roles. Each of these roles, define a collection of different related tasks. For instance, a Publisher has the permission of adding reports and creating folders for storing these reports.

Role assignments are inherited from the parent node, but users can break this permission of inheritance by simply creating a new role of assignment for each particular item. Note that a user can be a member of Content Manager Role in one report and might also be a member of another report for of Browser role.

Guidelines for granting access to different report server operations and items

1.    Review all predefined roles and determine whether they can be used as it is. If the user needs to adjust any tasks or define any additional roles, he/she should do it before assigning users to specific roles.

2.    Identify users or groups that require access to that particular report server, and on what levels. Most users are assigned to Browser role or Report Builder role. And only selective users need to be assigned for Publisher role. And Content Manager Role should only be assigned to the trusted officials.

3.    Use Report Manager for assigning roles in the Home folder for each group or user who requires access.

4.    Then go to Report Manager’s Site Settings page and create an assignment for system-level roles for each group or user, using predefined roles System Administrator and System User.

5.    Create additional assignments for assigning access to specific folders, reports, and other items. Avoid creating too many role assignments.

Who Sets These Permissions?

Initially, Report server can be accessed by the local administrator’s group or its members. Reporting Services are installed with only two default roles assignments which are used for system-level and granting item-level access to local administrators group and its member. These groups and members are responsible for assigning permission to other users.

How Are These Permissions Stored?

Report Server stores its role definitions and assignments in its database. If a user uses multiple programmatic interfaces or client tools, the access is subjected to the permissions which are defined as a whole for the report server. Role assignment is stored with all the items that they secure, that allow the user to move a database to a different report server without losing the defined permissions.

While MS SQL Server is a highly advanced platform, it still ends up getting plagued by data errors. Always keep a powerful SQL Server repair tool around to deal with unexpected data errors.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com

Be the first to comment
             

There are several database consistency checker commands. It is recommended that we should rarely execute FREEPROCCACHE in production environments. In this article, we will learn how to identify when database consistency checker commands were executed in your production environment.

Using SSMS

Messages available in the SQL Server Error logs can be an information or an error. At the time of an issue, this error log will help you to investigate quickly.

To identify when was the DBCC FREEPROCCACHE was executed on your SQL server, follow the steps mentioned below:

Step 1: Connect to your SQL Server database engine using SQL Server management studio.

Step 2: From the object explorer, expand the folder “Management” and then navigate to the subfolder “SQL Server Logs”. The number of archive log files available per SQL instance can vary from 6 to 99. By default, you can see 7 log files under “SQL Server logs” and it would include 1 current log file and 6 archive log files.

Step 3: To read data from a log file, double click and open it. A new window will open and in this window, you will see data on the left and checkboxes on the right which allows the selection of other archive log files.

 Step 4: You can now search for “DBCC FREEPROCCACHE” to reach the row that has Date, Source, and Message that was logged at the time of execution of DBCC FREEPROCCACHE. As an alternative, you could filter multiple logs. The entry would look like the following message:

SQL Server has encountered 1 occurrence(s) of cache store flush for the ‘Bound Trees’ cache store (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.

Using T-SQL

After connecting to your SQL Server database engine using SQL Server management studio, open a new query window and execute the query below:

 EXEC sp_readerrorlog 0, 1, 'DBCC FREEPROCCACHE' 

0 in the above query indicates that the current log file would be scanned for entries during the execution of DBCC FREEPROCCACHE

1 indicates that the log file used is SQL Server Error log file

Let us test it.

Before testing this, let us first recycle the log file so it is easy for us to view the content. To recycle the SQL Server Error log file, execute this command from a new query window.

sp_cycle_errorlog

And then execute this command:

DBCC FREEPROCCACHE

Now, if you check the SQL Server error log file, you will see entries as shown in the screenshot:

SQL Server Error Log

This script will recycle your SQL Server error log every day at 12:00 AM.

 USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Daily_Cycle_log',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'ECPL\naveenv', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'S1',
        @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 sp_cycle_errorlog',
        @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'S1',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20171113,
        @active_end_date=99991231,
        @active_start_time=0,
        @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:
 
GO

A regular usage of DBCC commands would help you to avoid SQL Server corruption. So make sure you have a maintenance plan to check the integrity of your databases.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment