Centralized SSIS solution to monitor failed SQL Server Agent Jobs across the enterprise

SQL Server Agent Jobs are crucial to any SQL Server environment as they are created and scheduled to perform critical business and operational tasks. As a database administrator (DBA) or developer, it is our responsibility to monitor SQL Server Agent Jobs to make sure they are running successfully, without any failures.  SQL Server provides several methods, such as job notifications, SQL Server agent alerts, etc., that can help monitor our SQL Server Agent Jobs, so that you can get an email or alert when a particular SQL Server agent job fails.  However, the problem with these monitoring solutions are that they are dependent on Database Mail, and if you are working for an organization where you are prohibited for enabling the Database Mail feature on a SQL Server instance, due to data security reasons, then the only option is to check the status of SQL Server Agent Jobs by manually reviewing the job status in Job Activity Monitor, which is a time consuming task, especially if you are managing a large number of SQL Servers.

Check out my article here, in which I showed steps that you can follow to design your own centralized custom solution using SQL Server Integration Services that will email a report that contains the list of all SQL Server Agent Jobs across all of the SQL Servers that failed in the last hour.

This article is published on MSSQLTips.com.

Advertisements

Identify the cause of SQL Server blocking

In my previous article (Different techniques to identify blocking in SQL Server) on MSSQLTips.com, I discussed about locks and blocks, and presented you with an overview on how to troubleshoot and resolve blocks using dynamic management views and Activity Monitor. After I wrote this article, I received several emails from readers asking how they can use the information returned by these dynamic management views (DMVs) to identify SPIDs and other useful information about the processes that are actually causing blocking on a SQL Server instance.

Check out my latest article (Identify the cause of SQL Server blocking) on MSSQLTips.com, in which I shared the query that will help you to quickly identify SPIDs and other useful information about the processes that are causing blocking on SQL Server instance.

Consolidating Error Logs from multiple SQL Servers using SSIS

SQL Server stores all informational messages, warning messages, and errors in operating system and application log files. As a database administrator (DBA), it is our responsibility to review the information in the error log files on a daily basis for any errors that have occurred in our SQL Server environment. However, manually reviewing the log files daily is not an easy task in a busy SQL Server environment, and it can quickly consume a large part of your day. Therefore, most DBAs tend to skip this when they are busy with other production problems.

Check out my article here, in which I showed how we can consolidate error log entries from multiple servers into a central location, and then report on them from central location.

This tip is published on MSSQLTips.com.

How to migrate a SQL Server database to a lower version

Problem

After recently upgrading a SQL Server instance to SQL Server 2012 a few days ago, you noticed that your application is not functioning properly. You decided to roll back the upgrade by downgrading the SQL Server database engine to SQL Server 2008 R2. After the downgrade of the database engine, you are unable to attach the databases or restore the backups of the databases, even though the database compatibility level is set to the downgraded version of SQL Server. You receive the following error message, when you attempt to restore the database:

Msg 1813, Level 16, State 2, Line 1 
Could not open new database ‘DatabaseName’. CREATE DATABASE is aborted. 
Msg 948, Level 20, State 1, Line 1 
The database ‘DatabaseName’ cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

This error message is generated because SQL Server automatically upgrades the database, when you restore or attach the database from lower version to higher version. SQL Server does not allow you to restore or attach a database from a higher version of SQL Server to a lower version of SQL Server.

Solution

Checkout my tip (ie. How to restore a SQL Server database to a lower version) in which I discussed one time procedure which we can follow to downgrade the database from a higher version (SQL Server 2012) of SQL Server to a lower version (SQL Server 2008 R2) of SQL Server.

This article is published on MSSQLTips.com.

SQL Server Service Broker error database cannot be enabled

Problem

The SQL Server Database Engine returns the following error message after a service broker enabled database is restored to the same SQL Server instance with a different database name and the T-SQL command (ALTER DATABASE ‘DatabaseName’ SET ENABLE_BROKER) is issued to enable the Service Broker for this restored database.

Msg 9772, Level 16, State 1, Line 1 
The Service Broker in database “DatabaseName” cannot be enabled because there is already an enabled Service Broker with the same ID. 
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Solution

Checkout my article (ie. SQL Server Service Broker error database cannot be enabled) in which I discussed how you can fix this error message. This article is published on MSSQLTips.com.

Different techniques to identify blocking in SQL Server

SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many customers, there is a strong possibility that conflicts arise because different processes access the same resources at the same time. A conflict in which a process waits a release the resource is a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it.

Checkout my tip (i.e. Different techniques to identify blocking in SQL Server) in which I discussed various techniques for troubleshooting and resolving blocks in SQL Server. This tip is published on MSSQLTips.com.