August 2013 | SansSQL

Wednesday, August 28, 2013

Backup databases with COPY_ONLY using Maintenance Plans - SQL Server 2012

Prior to SQL Server 2012, we were not allowed to take COPY_ONLY backups using the Maintenance Plans. We could use this option only with the Backup Database GUI or using T-SQL scripts.
In SQL Server 2012, this is one of the new option that allows us to take COPY_ONLY backups using the Maintenance Plans.

Tuesday, August 27, 2013

Unable to create restore plan due to break in the LSN chain

Recently one of my colleague was working on restoring a database in SQL Server 2012 and the process had one Full database backup and one Differential Backup.
At first everything worked when they try to restore the full database backup with "No Recovery" and when they are trying to restore the differential backup, they were facing an issue which states "Unable to create restore plan due to break in the LSN chain."

Monday, August 26, 2013

Manage SSIS packages saved in multiple instances of SQL Server

Consider a situation where you have multiple instances of SQL Server installed on a single server and each instance has multiple SSIS packages store within the msdb.

Also we know that the SQL Server Integration services is not instance aware which means we cannot install more than one instance of SSIS on one server.

Now the question is how can we manage SSIS packages stored on the different SQL Server instances?

The Solution for this is very simple and can be achieved by modifying the SQL Server integration services configuration file to include the second instance. By default, the configuration file will include the SQL Server instance which was installed first and we have to add the additional SQL Server instances to this config file.
The name of the config file is "MsDtsSrvr.ini.xml" and will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

Before modifying the file

Now for this file, I will add one more SQL Server instance so that I can manage SSIS packages from 2 SQL Server instance with a single instance of integration services.
After modifying the file we need to restart the SQL Server integration server services in order to reflect the changes made.

Sunday, August 25, 2013

Login timeout expired. The SQL Server instance specified in SSIS service configuration is not present or is not available

We all know that SQL Server Integration services is not cluster aware. This means that we cannot set the SQL Server Integration Services to failover automatically. But still in many situations we are required to have SSIS installed on a cluster and make it work when the database engine failover or failback.
To achieve this, we have to install SSIS on all the nodes of the cluster.
Okay, I have a 2 node cluster and I have installed SSIS on both the nodes.
What next?
When we install the SSIS, the configuration file by default refers to "." which means local default instance. But in the clustered environment, we never install the database engine with the node name and hence when we try to open the Integration services it fails with the below error message.
To fix this error, we have to modify the SSIS configuration file "MsDtsSrvr.ini.xml" to refer to the correct SQL Server Instance Name.
This file will be usually located at "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" and for SQL Server 2005 at "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

Before editing the file

After editing the file, restart the SQL Server Integration services to refer to the new SQL Server instance name

Saturday, August 24, 2013

Fix - The SSIS subsystem failed to load

The subsystem failure is a common error which occurs when an SQL Server instance is migrated.
When we do the migration, we usually miss to install the SQL Server binaries on the same location as it was in source. Because of this the SQL Server jobs which uses SSIS, powershell, etc... subsystems will fail.

Wednesday, August 21, 2013

T-SQL Query to find last run status of scheduled Jobs

Here is a T-SQL query to find the last run status of all the scheduled jobs in SQL Server.
This query will be handy when you are not able to access the "Job Activity Monitor"
USE msdb
GO
SELECT DISTINCT SJ.Name AS JobName, SJ.description AS JobDescription,
SJH.run_date AS LastRunDate, 
CASE SJH.run_status 
WHEN 0 THEN 'Failed' 
WHEN 1 THEN 'Successful' 
WHEN 3 THEN 'Cancelled' 
WHEN 4 THEN 'In Progress' 
END AS LastRunStatus
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJH.job_id = SJ.job_id and SJH.run_date = 
(SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)
ORDER BY SJH.run_date desc

Sunday, August 18, 2013

T-SQL Query to find currently running jobs

Here is a T-SQL query to find the currently executing jobs.
The output of this query will be the list of jobs that are currently running along with the number of seconds it is been running.
SELECT  J.name as Running_Jobs,  
  JA.Start_execution_date As Starting_time,
        datediff(ss, JA.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity JA