Monday, September 17, 2012

ISSUE#0002:0x8004D01B " The Transaction Manager is not available.". The DTC transaction failed to start.

There're some TransactionOption can be set in SSIS Package (Package Level, Container Level, Task Level...):
  • Required - if a transaction exists join it else start a new one
  • Supported - if a transaction exists join it (this is the default)
  • NotSupported - do not join an existing transaction
But sometime when you set Required to a container or other component and to execute the package, error information will show you:

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Just use an example to show how to use Trasaction in SSIS package and solve this issue.

1. There're two SQL Tasks in a container, the first task is to TRUNCATE all data in 'Documents' table, the second task is to insert one record to table 'Documents'. I just use a wrong insert statement in the second task, so the second task will throw an error when I execute this package. We use default TransactionOption - Supported to all components.



 
The result is the 'Insert Data' task failed, but the 'Truncate Data' works and all data in table have been deleted.



2. Change TransactionOption of Sequence Container to 'Required'.

 

 

Execute this package and get this error
 

 
The error information show - The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

3. This error information tells you need to start your MSDTC Service. Open the cmd prompt with Administrator role.

Try 'NET START MSDTC' to start your MSDTC Service.


Then execute this package again.

 
Task 'Insert Data' still throw an error but the 'Truncate Data' will be roll back. Because they're in a same transaction when Sequence Container set its TransactionOption from 'Supported' to 'Required'.

Wednesday, September 5, 2012

SQL T001- How to use SQL Profiler to get DeadLock information

 Test - How to use SQL Profiler to get the DeadLock Information.

First, to create two tables and insert some testing data.

-------------------------------------------------
-- Script 1 - Create testing table first.
-------------------------------------------------
USE SSISDemoDB
GO

SET NOCOUNT ON
GO
 
IF OBJECT_ID('DeadLockTable1') IS NOT NULL
DROP TABLE DeadLockTable1

IF OBJECT_ID('DeadLockTable2') IS NOT NULL
DROP TABLE DeadLockTable2
GO

CREATE TABLE DeadLockTable1
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   NAME VARCHAR(50)
)

CREATE TABLE DeadLockTable2
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   NAME VARCHAR(50)
)
GO

-- Insert some testing data
INSERT INTO DeadLockTable1
SELECT NAME
FROM sys.objects
 
INSERT INTO DeadLockTable2
SELECT NAME
FROM sys.objects

Run this script to create tables and data.

Second, to create two trasactions in two scripts, don't execute it when you finish it.

------------------------------------
-- Script 2
------------------------------------
USE SSISDemoDB
Go
 
BEGIN TRANSACTION

   -- Update table 1 first
   UPDATE DeadLockTable1
   SET NAME = 'Test1'
   WHERE ID > 0
  
   -- Pause 5 seconds
   WAITFOR DELAY '00:00:05'
 
   -- Update table 2
   UPDATE DeadLockTable2
   SET NAME = 'Test2'
   WHERE ID > 0

COMMIT TRANSACTION
GO
----------------------------------------
-- Script 3
----------------------------------------
USE SSISDemoDB
Go

BEGIN TRANSACTION
  
   -- Updata table 2
   UPDATE DeadLockTable2
   SET NAME = 'Test2'
   WHERE ID > 0

   -- Pause 5 seconds
   WAITFOR DELAY '00:00:05'

   -- Update table 1
   UPDATE DeadLockTable1
   SET NAME = 'Test1'
   WHERE ID > 0
 
COMMIT TRANSACTION
GO
Third, open SQL Profiler and new a trace

You can output the trace to a file or save data into a table.


We need to add some event for this trace, make sure the below event you have selected.

Deadlock Graph
Lock:Deadlock
Lock:Deadlock Chain
RPC:Completed
SQL:BatchCompleted
SQL:BatchStarting
 

Execute Script 2 and Script 3.

After 5 seconds, the message from script 2 show successfully updated information.
But script 3 return an error information -

Msg 1205, Level 13, State 51, Line 13
Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


In this case, there's a Deadlock caused by your query.

Find the Deadlock Graph on Profiler and you can see a query flow which can reflect the reason of the Deadlock and system end the left trasaction and keep the right one.

 
 
 


It helps us know which statements caused the Deadlock and we need to analyze the reason and tune our scripts. The Deadlock wouldn't impact the process, because SQL Server will choose a connection to end it.

Before the application go live, we may use SQL Profiler to trace the performance and check if there's any Deadlock in system. You can save the trace information into a table and use SSIS Package or SQL Job to inform team there's something unexpected in our system.


Sunday, September 2, 2012

ISSUE#0001:0x80041003&0x80041010 Cannot connect to WMI provider.You do not have permission or the server is unreachable.

I got an error when I want to open my SQL Server 2008 R2 Configuration Manager. Just few month ago I upgraded my SQL Server 2008 R2 to SQL Server 2012, I am not sure if this operation impacted it.

 
Error Information

"Cannot connect to WMI provider.You do not have permission or the server is unreachable.Not that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.Invalid class [0x80041010]".

Find a solution by Google..

1. Find the *.mof file under your SQL Server 100(Version 2008) or 110(Version 2012)
2. Use command "mofcomp"...


But I got another error:
An error occurred while processing item 1.... Error Number: 0x80041003, Facility: WMI Description: Access denied Compiler returned error 0x80041003

Googling it..and find the reason that I didn't run this cmd as Administrator role, so correct it..

Right click cmd.exe and Run as administrator

 
 
Again..


Done!!!

Now I can open my SQL Server Configuration Manager successfully.

When I tried to open my SQL Server 2012 Configuration Manager, I saw the same error, then I just followed the above steps and solved the problems.