Stop successful backup logging info in SQL Server Error Log

The SQL Server error log contains systems events and user defined events have information, user will use this log to troubleshoot problems related to SQLServer.

SQL Server error log will have information about

• Backup and restore operations
• batch command info
• processes info
• automatic recovery message
• SQLServer stop and start info
• kernel messages
• Server –level error messages ...etc

But I see that SQL Server Error log is mainly full with “successful backup logging” due to frequent backups. In production environments, I have seen frequent log backups due to RPO (Recovery point objective) and Successful backup information will be logged into SQLServer Error log.

2013-11-18 18:15:08.08 Backup Database backed up. Database: EXP, creation date(time): 2008/07/09(16:46:27), pages dumped: 3732, first LSN: 10550:280:300, last LSN: 10550:408:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\\SQLServer_Backup\Full_Backup\EXP\EXP_backup_2013_11_18_181504_1347670.bak'}). This is an informational message only. No user action is required.
2013-11-18 18:45:06.40 Backup Log was backed up. Database: EXP, creation date(time): 2010/12/14(14:30:59), first LSN: 73542:11608:1, last LSN: 73542:11650:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\SQLServer_Backup\Log_Backup\EXP\EXP_backup_2013_11_18_184503_3906746.trn'}). This is an informational message only. No user action is required.

You can recycle the SQLServer error log to decrease the ERRORLOG size using below link, but it will not solve your problem completely.

Create New Error Log or Recycle Error log in SQLServer
http://www.sqlserver-expert.com/2012/06/create-new-error-log-or-recycle-error.html

It is really difficult to read the ERRORLOG due to this backup information, and we can stop logging successful backup information by using one of the below solution

1. DBCC TRACEON

Run below command to turn off/stop the successful backup entries.

DBCC TRACEON (3226,-1)


If you restart the SQL Server the changes will be gone. If you want to make these changes permanent then you need to specify the flag at start-up parameter.

2. SQL Server startup parameters

You can add trace flag “-T3226” to SQLServer startup parameters to suppress successful backup information

• SQL Server Configuration Manager, Select SQL Server Services
• Right click on SQL Server Instance service go to properties and select "startup parameters" tab
• Enter “-T3226” value in “Specify a startup parameter” and click Add
• Click on Apply.

 

You need to restart SQL Server in order to affect the changes.


Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Install SQL Server Reporting Services (SSRS)

SQL Server reporting services (SSRS) is a report generating software system, it has full range of ready to use tools and services to create, deploy and manage reports for your organization.

The reporting service is not cluster-aware, must be installed locally. But scale-out deployment provides a way to share a database. If one report server goes down the other reporting servers will continue to work for your reports.

User can install Reporting Services in Native or SharePoint mode.

1. Native – User can use as stand-alone application with default configuration
2. SharePoint - User can integrate SSRS with SharePoint

In SQL Server 2012, reporting services introduces Power view – an interactive data exploration, visualization and presentation experience and provides drag and drop and ad hoc reporting for your business needs

SQL Server 2012 reporting services Installation Steps: 

1. Launch SQLServer Installation Center à Go to Installation tab
2. Click on New SQL Server Stand-alone installation or add features to an existing installation
3. Select Installation Type : Perform a new installation of SQLServer 2012
4. Product Key: Enter the product Key
5. License Terms : Click on check boxes to accept the license terms
6. Setup Role: Select one of the below feature based upon your requirement
· SQL Server Feature Installation
· SQL Server PowerPivot for SharePoint
· All Features with Defaults
7. Feature Selection : Reporting Services – Native
8. Installation Rules: You should not see any warning/failures
9. Instance Configuration: Choose the name that you want
10. Disk space requirements: Review available and free space
11. Server configuration: Select Service account
12. Reporting Services Configuration: Install only
13. Error Reporting : Check the box if you want send the error reports to Microsoft
14. Installation and Configuration Rules: You should not see any warning/failures
15. Ready to Install: Click on “Install Button”
16. Installation Progress: You will see that installation progress info
17. Complete

Before you configure SSRS You need to have a SQLServer instance on a computer where you will host Report server or you can use remote SQL Server instance.

Downloads

You can download Microsoft SQL Server 2012 Report Builder using below link
http://www.microsoft.com/en-us/download/details.aspx?id=29072

You can download Microsoft SQL Server 2008 Reporting Service Report Builder 2.0 using below link
http://www.microsoft.com/en-us/download/details.aspx?id=24085

You can download Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies
http://www.microsoft.com/en-us/download/details.aspx?id=1848

SQL Server 2000 Reporting Services Service Pack 2 (SP2)
http://www.microsoft.com/en-us/download/details.aspx?id=20497

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube

Hold a chance to Win FREE copy of “Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube”, just by LIKING and commenting!

For the contest we have 4 e-Book copies of Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube, to be given away to 4 lucky winners.

To Win your copy of this book, all you need to do is LIKE the Facebook page https://www.facebook.com/SQLServerExpert and come up with a Comment highlighting the reason "why you would like to win this book”.

The contest is valid for 10 days, and is open to everyone. Winners will be selected on the basis of their comment posted and all rights reserved.

About the book: 



Microsoft SQL Server Analytical Services, or SSAS, is an analytical processing OLAP tool which allows users to analyze business data quickly and easily.

In this book you will get started with SQL Server cube development. You will start by learning how to install SQL Server 2012 and create a development environment. You will then finish by diving into the development and design of dimension and cubes in SQL Server.

This book starts by jumping into the new and important features of Microsoft SQL Server 2012. Afterwards we will run through the installation of SQL Server then how to check the validation of SQL Server and security consideration during and after installation.

After you have SQL Server running, you will get started with SQL Server cube development, first by creating a development environment, then by developing and designing dimensions and cubes in SQL Server.

Moving on to administration and user management of SQL Server 2012 you will then build your own SQL Server development environment and create your own dimensions and cubes. You will then finish with a journey through further tutorials and guidance on continuing and progressing with SSAS cube development.