Tag Archives: Error Logs

Suppressing Successful backup Information in SQL Server Error Log

Posted on by
1

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 @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

 

SQL Server fails to Start-Troubleshooting the failure

Posted on by
Reply

Many times I see people asking in forums about SQL Server not started and how to troubleshoot the reasons for the failure to start the SQL Server.

This video explains the steps to troubleshoot the failures and find the error of a SQL Server startup failure. 

YouTube Preview Image

As always, you can give your feedback as an email to me (bru@learnsqlwithbru.com) or thru comments.

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

–Bru Medishetty

View SQL Server Error Logs through T-SQL

Posted on by
4

When SQL Server error log is not initialized at a regular interval causing the error log file to grow into a huge file. Viewing that in the SQL Log File Viewer would be a time consuming task. 

This article will show how to view the SQL Server error log from a query analyzer in 2000 or query editor in later versions. As an additional benefit this code filters the log file and displays only the past 24 hours (default code). 

To view SQL Server error logs in SQL Server 2000

DECLARE @HOURS INT
SET @HOURS = 24

CREATE TABLE #ErrorLog
(ErrorLog Varchar(1000),
ContinuationRow INT)

INSERT INTO #ErrorLog
EXEC sp_readerrorlog

DELETE FROM #ErrorLog
WHERE
(LEFT(LTRIM(ErrorLog),4)
NOT LIKE DATEPART(YYYY,GETDATE())
AND ContinuationRow = 0)
OR ContinuationRow = 1
OR LEN(ErrorLog) < 25
OR
(CAST(LEFT(LTRIM(ErrorLog),23) AS DATETIME)
< CAST(DATEADD(HH,-@HOURS,GETDATE()) AS VARCHAR(23)))

SELECT * FROM #ErrorLog 

DROP TABLE #ErrorLog

To view SQL Server error logs in SQL Server 2005 / 2008 / 2008R2

DECLARE @HOURS INT
SET @HOURS = 24

CREATE TABLE #ErrorLog
(LogDate DateTime, ProcessInfo Varchar(50),
[Text] Varchar(4000))

INSERT INTO #ErrorLog
EXEC sp_readerrorlog

DELETE FROM #ErrorLog
WHERE LogDate < CAST(DATEADD(HH,-@HOURS,
GETDATE()) AS VARCHAR(23))

SELECT * FROM #ErrorLog 

DROP TABLE #ErrorLog

The script can be changed to display only data for the past n # of hours by changing the value of @Hours.

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

—Bru Medishetty