Suppressing Successful backup Information in SQL Server Error Log

Some time back, I was administering a SQL Server 2005 instance which hosts a huge number of Databases. The instance is in production environment and all the databases are set to be backed up at regular intervals of 10 minutes. Due to this, SQL Server Log is flooded with backup information resulting the SQL Server Log to grow enormously. As part of monitoring, I look at the entries in SQL Server Log and it is a tough task to weed out the entries related to successful database backups. These are the steps I had to perform to supress the successful backup information in SQL Server Logs.

To understand better let us take a look at the problem first and then work on solving it.

SQL Server runs a default trace which keeps track of certain events and writes them to SQL Server Log when there is an important information such as a database restore, change in recovery model of a database or a failure login. 

The following images display some sample log entries found in a log viewer. The image displayed contains an entry about a failed login attempt to the SQL Server.

This below image displays an entry about a change in the recovery model for a database. 

Here is another example, the below image displays the error log indicating that a profiler trace has been started and stopped by user.

Monitoring the SQL Server log from time to time is very important since it has vital information about the SQL instance. Many times, the information found in these error logs is important for troubleshooting SQL Server issues. Having too many entries in the SQL Server Log causes delay in loading the error log in log viewer, moreover it is difficult to find the error messages reported in the error log due to high number of log entries. SQL Server Error Log is recycled automatically every time SQL Server starts, due to this, error log grows in huge size causing trouble to find the vital information about the SQL instance.

On a SQL Server which hosts 20 databases, assuming that transaction log backups are scheduled every 10 minutes, would result in 2880 entries each day due to successful backups being written in error log. This should be avoided in order to find out other entries easily in log viewer.

Next we move on to the steps to solve this problem.

This problem can be solved in 2 different ways. One way of solving this by runnig DBCC TRACEON command.

Solution 1

Below is the screenshot of the command. What we are doing in this command is to turn off all successful backup entries into the SQL Server. Note that any failed backups are still logged into the error log..  The next line is the command to recycle the SQL Server Error Log, so that we would have a clean error log from that point onwards. This solution is good until the SQL Server is restarted the next time. So as long as the SQL Instance is not restarted, we will not be seeing the successful backup entries in the Error log. 

Below is how the Error Log looks, after it has been recycled..

Solution 2

The procedure involved in this solution is to add a trace flag to the SQL Server startup. This solution is permanent, that is, the successful backup information will not be written in the error log, unless the trace flag is removed manully from the SQL Server startup parameters..

In order to do this, we need to log into Services console and find the SQL Server service and right click, choose porperties. In the Advanced tab, under startup parameters. Go to the end of the parameters and add a semicolon ” ; “, followed by -T3226. Save the modifications by clicking Apply and OK.

You should see a warning / confimation message  indicating that the changes will take effect only when the SQL Server is restarted next time. Click OK and when possible, restart the SQL Server.

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty


Learning SQL Server Performance Tuning – Part One

I wrote a blog in January 2010, called Performance Tuning in SQL Server. It was kind of introductory non-technical blog. (If you have time I would recommend you read it, would not take much time to digest it). In this short blog we will learn various steps involved in SQL Server performance tuning.

When dealing with performance tuning SQL Server, we can broadly classify it into 2 major areas; Physical Server related and SQL Server related. I will explain what does these areas consists of in more detail shortly, but remember that both these areas needs to be addressed in order to reach the optimum performance from the SQL Server Databases.

Tuning physical server involves tuning those underlying hardware components that define the physical server on which the SQL Server Instance is running OR SQL Server needs to be installed on. Primary hardware resources that needs to be monitored and tuned are Memory, Processors, Disk Drives and Network. 

SQL Server tuning invloves those that can be configured at the Instance and database level in SQL Server and more importantly involves in T-SQL query tuning.

In the next part of this series we shall look more details with respect to both these areas.

Note: Links to other parts in the series will be included here as and when they are published.

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

Changing SQL Server Authentication Mode

This article explains the procedure, how to change the authentication mode in SQL Server.

Authentication mode of a SQL Server instance is the deciding factor how the SQL Server authenticates the users and logins. SQL Server supports 2 authentication modes: Windows Authentication Mode & SQL Server and Windows Authentication Mode. There are advantages and dis-advantages in choosing these authentication modes, which will be covered in a seperate blog later. Let’s take a look at the steps to change the SQL Server Authentication Mode.

We start by right clicking on the SQL Server instance and choose Properties from the pop-up menu, as shown in the below image.

Then, SQL Server properties window is displayed. (as shown below).

Choose Security page in left pane of the dialog box. Now in the right side pane, in the top section (image below) you have the Server authentication section under that the 2 authentication modes are listed as an options, (at any point onnly one of them can be selected). Choose the appropriate authentication mode you would like the SQL Server set to and click OK. 

The next step is to restart the SQL Server.

Do you like this site? Like our FB page @\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty