SQL SERVER – How to Identify a DB is Using Cross Database Transactions?

I cannot iterate this often, some of the best learnings always come from times you least expect a question to come from. Recently, when I was at a conference in Delhi, one of the attendees came to me to ask a question. The conversation was as below:

Attendee: Hi Pinal.

Pinal: Hi to you too.

Attendee: Thanks for the blogs on SQLAuthority. I am a regular reader of the same.

Pinal: Thanks for your kind words.

Attendee: I have a quick question for you and hope you are fine.

Pinal: Sure, shoot it. I would love to help you buddy.

Attendee: Pinal, I work in a financial institution and I manage a number of databases in the production DB.

Pinal: Sure, go on. Isn’t that a good thing.

Attendee: I know it is a good thing, but recently the management was paranoid of running different application databases on the same massive production box. So they asked me a question.

Pinal: Great, so you have done some consolidation of your environment.

Attendee: Yes, indeed – we did consolidation of our servers and few DBs into a box and that is where the management is paranoid of security. They asked me, if there is any way to know if a database is involved in cross-database transactions because it is important to know. They don’t want databases to be involved in such calls in this new server.

Pinal: Hmmm, that is interesting.

Attendee: To start with, they want to know if any of the databases are involved – then they will look at the next steps of hardening the server.

Pinal: I am sure there is someway. Meet me in the morning tomorrow and I will try to get you something. And BTW, People like you who ask me such questions are the real heroes of this blog. I am glad you walked up to ask this question. Thanks again for your time.

After this question, I was not able to sleep and on quick digging on how distributed transactions work – I gave him a simple script when run on a DB will let him know if it was involved in cross-database calls.

IF EXISTS(SELECT 1 FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_PREP_XACT' AND [Master DBID] <> 0)
PRINT 'Based on the active part of the transaction log read, there is evidence that this database has participated in cross-database transactions.'
ELSE
PRINT
'Based on the active part of the transaction log read, there is no evidence of this database having participated in cross-database transactions.'

This solution is based on an undocumented command of fn_dblog() which reads the Transaction log records. It has been safe and I have seen many use this to great effect. You can also use yet another predicate to look at the same.

-- Or We can use this alternative predicate:
SELECT 1 FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_PREP_XACT' AND [Master XDESID] <> '0000:00000000'

Both these options are based on the fn_dblog() function and your active part of Transaction Log. I am sure this is not a foolproof mechanism but I am sure it is a good start for folks who want to know about this information.

Having said that, I am keen on knowing if any of you have ever encountered such requirements and what has been your solution for the same. Do drop a line over the comments and I would love to learn from you too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Knowing Nested Transactions Behavior with SQL Server

SQL Server is an interesting database system that baffles me every single day that I get to learn something new. This blog has been my way to share these learnings. Every time I write something new, my blog readers have been kind enough in pushing me with interesting comments that I get to learn a lot more. I am a firm believer that learnings get enhanced every time share something.

This blog is an extension of what I wrote a couple of weeks back SQL SERVER – Error – Msg 6401, Level 16: Cannot roll back Transaction. One of my blog readers asked me, doesn’t SQL Server create or start a new transaction whenever we do these nested transactions? This was a valid question and understandable. I was thinking of a way to show the concept that SQL Server will not create any new transaction even when it is a nested format. Finally, I came up with this script to show the working.

We will create the script in the following sequence:

  • Create our database and a dummy table
  • Create a transaction and enter some value into the table
  • Create a nested transaction and enter some value into the table
  • Look at the Transaction Log and Transaction ID to see if they are any different
  • Create a ROLLBACK operation and show what is entered in TLogs

-- Create our database for testing
USE MASTER;
GO
CREATE DATABASE TransactionsDemo;
GO
USE TransactionsDemo;
GO
-- Create a table for testing
CREATE TABLE tbl_SQLAuth (Col1 INT);
GO
SET NOCOUNT ON;
GO
ALTER DATABASE [TransactionsDemo] SET RECOVERY SIMPLE WITH NO_WAIT
GO
CHECKPOINT

Let us create the data into our table with transactions.

-- Create an explicit transaction and insert single row
BEGIN TRAN OuterTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (1);
GO
-- Create an explicit so called nested transaction and insert single row
BEGIN TRAN InnerTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (2);
GO

At this point, if you check the transaction log records – it shows an interesting note. This can be queried using the undocumented command fn_dblog().

-- Look at the TLogs for entries now.
SELECT Operation, [Transaction ID], Description FROM fn_dblog(NULL, NULL)

If you check the Transaction ID column, you can see the values for both the records point to the same transaction ID. So there is no difference when it comes to creating nested transactions.

To extend our understanding, let us try to rollback the transaction. This will rollback both the rows with a compensating record in the TLog. So let us see what happens.

-- Rollback the OuterTransaction
ROLLBACK TRAN OuterTransaction;
GO

Let us look at the TLog entries now.

As you can see both the rows have been rolled back using a COMPENSATION record for each of the rows that were inserted in the Transaction log. The final ABORT_XACT confirms the completion of our Transaction.

Do let me know if you have ever learnt the transaction context ever like this? It is sometimes fun when someone pushes us to learn the basics the hard way. Hope you like this blog and learnt something new today. Let us learn together.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identify Oldest Active Transaction with DBCC OPENTRAN

I recently received a question on my Facebook page-

“Pinal,

How do I know if there is any open transaction in my database? I recently wanted to restart my server, but my manager asked me to first check if there is any open transaction in database before I restart the system.”

Very interesting question. The answer is very simple.

You can execute the following script to see if there is any open transaction. First run following script on any of your development server.

BEGIN TRAN
UPDATE
Person.Address
SET AddressLine2 = ''
WHERE AddressID = 112

Now open another connection in MySQL and execute following script.

DBCC OPENTRAN

Above DBCC will return following output where it will return the details for the oldest active transaction. For example, here is the sample output in my system.

Transaction information for database 'AdventureWorks2012'.
Oldest active transaction:
 SPID (server process ID): 57
 UID (user ID) : -1
 Name : user_transaction
 LSN : (163:324:1)
 Start time : Jan 16 2014 4:34:58:813PM
 SID : 0x0105000000000005150000001650720d0cca921474e68766e8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you are looking for a script where you can get details for all the transactions with text and plan, here is the excellent script from SQLskills team, I use it all the time when I have to work with Active Transactions.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – TRANSACTION, DML and Schema Locks

Today we will be going over a simple but interesting concept. Many a time, I have come across the lack of understanding on how the transactions work in SQL Server. Today we will go over a small but interesting observation. One of my clients had recently invited me to help them out with an interview for their senior developers. I had interviewed nearly 50+ candidates in a single day. There were many different questions, but the following question was incorrectly answered most of the time.

The question was to create a scenario where you can see the SCHEMA LOCK. The interview panel initially thought that this might be a very easy question for this particular interview. I, however, insisted them to keep this question for time being and then remove it from the list of interview questions only when at least 10 candidates got it right. Contrary to our expectations, we never reached a point where we had to remove this question from the list!

Let us see a simple example regarding how to create a schema lock. The answer I was looking for is as follows: create a situation where the Schema is modified in the transaction and check the status of the object or session before the transactions are committed or rolled back.

Run the following code in Query Session 1:

USE AdventureWorks
GO
BEGIN TRANSACTION
GO
CREATE PROCEDURE mySP
AS
SELECT
1
GO
SELECT OBJECT_ID('mySP') ObjectID
GO

The above script will give us the objectID of the created stored procedure. In this case, the received ObjectID is 1300199682; this can be different for your execution.

Run the following code in Query Session 2:

USE AdventureWorks

GO
SELECT *
FROM sys.procedures
GO

This query will never finish running as in Session 1, where we have created the Stored Procedure. The name is already listed in the sys.procedures, but the transactions in Session1 are not yet committed.

If you run the following code, it will also not return any results even though we have received the ObjectID in Session 1.

USE AdventureWorks
GO
SELECT OBJECT_NAME(1300199682)
GO

Run the following code in Query Session 3:

Now to confirm that a schema lock is created, we can check the dynamic management views dm_tran_locks.

USE AdventureWorks
GO
SELECT *
FROM sys.dm_tran_locks
GO

We can clearly see from the example that there is a Sch-M (schema modify) lock over our ObjectID.

You can specify the where condition to this DMV as we are know the ObjectID here.
USE AdventureWorks
GO
SELECT request_type, request_mode, resource_associated_entity_id, request_type
FROM sys.dm_tran_locks
WHERE resource_associated_entity_id = 1300199682
GO

From above example, it is very clear that running DML code in the transactions create a schema modification lock until the transactions are over.

If you run the COMMIT or ROLLBACK statement in Session 1, the Queries in Session 2 will complete right away.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior

Earlier, I had written on SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON, and I received a comment regarding when this option can be useful. On the same day, I met Jacob Sebastian—my close friend and SQL Server MVP, I discussed this question with him. During our discussion, we came up with following example.

When we have situation where we are dealing with INSERT and TRANSACTION, we can see this feature in action. Let us consider an example where we have two tables. One table has all the data and the second table has partial data. If you want to insert all the data from the first table to the second table and insert all nonduplicate values, you can use this feature.

Let us first create a table that has a duplicate record based on the FirstName.

Option 1: With IGNORE_DUP_KEY set OFF

Once a table is created, create another table and a nonclustered index on it with the option IGNORE_DUP_KEY set OFF. Try to insert data from another table into this table by using the INSERT… SELECT statement.

As there are duplicate values, the insert will fail with error and not insert any single row in the table.

Option 2: With IGNORE_DUP_KEY set ON

Now, once again create new table and create nonclustered index on it with the option GNORE_DUP_KEY set ON. Try to insert data from another table into this table using INSERT… SELECT statement.

Even though there are duplicate values in the table, the rows in the table will be inserted; however, the duplicate values will not be inserted and ignored. A warning message will be generated with the script, but the process will not be stopped.

I hope that this example clarifies the use of this feature. Again, as I said earlier, this is a business logic related issue, and it should be used based on your need.

The complete script from the example is given here.

USE tempdb
GO
CREATE TABLE DupData (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
INSERT INTO DupData (ID,FirstName,LastName,City)
VALUES(1,'John','Doe','LA');
INSERT INTO DupData (ID,FirstName,LastName,City)
VALUES(2,'Joe','Doe','LA');
INSERT INTO DupData (ID,FirstName,LastName,City)
VALUES(3,'John','Doe','LA');
INSERT INTO DupData (ID,FirstName,LastName,City)
VALUES(4,'Joseph','Doe','LA');
GO
-- Check data from Test Table.
SELECT *
FROM DupData
GO
-- Option 1 - With IGNORE_DUP_KEY set OFF
CREATE TABLE DupIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
--Create Unique Index on Table
CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName]
ON [dbo].[DupIndex]
(
[FirstName] ASC
) ON [PRIMARY]
GO
-- Insert will throw an error
-- Error will rollback transaction
INSERT INTO DupIndex (ID,FirstName,LastName,City)
SELECT ID,FirstName,LastName,City
FROM DupData
ORDER BY ID
GO
-- Table with contain NO row
SELECT *
FROM DupIndex
GO
DROP TABLE DupIndex
GO
-- Option 2 - With IGNORE_DUP_KEY set ON
USE tempdb
GO
CREATE TABLE DupIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
--Create Unique Index on Table with IGNORE_DUP_KEY set ON
CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName]
ON [dbo].[DupIndex]
(
[FirstName] ASC
) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
GO
-- Insert will throw warning
-- Warning will not insert duplicate row
INSERT INTO DupIndex (ID,FirstName,LastName,City)
SELECT ID,FirstName,LastName,City
FROM DupData
ORDER BY ID
GO
-- Table with contain three rows
SELECT *
FROM DupIndex
GO
-- Clean up
DROP TABLE DupIndex
DROP TABLE DupData
GO

Let me know what you think of this option and the type of situation in which you will use this.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction

Few days ago I wrote an article on the myth of table variable stored in the memory—it was very well received by the community. Read complete article here: SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth.

Today, I am going to write an article which follows the same series; in this, we will continue talking about the difference between TempTable and TableVariable. Both have the same structure and are stored in the database — in this article, we observe the effect of the transaction on the both the objects.

DECLARE @intVar INT
SET
@intVar = 1
SELECT @intVar BeforeTransaction
BEGIN TRAN
SET
@intVar = 2
ROLLBACK
SELECT
@intVar AfterRollBackTran

It is a very well known fact that variables are unaffected by transaction as their scope is very limited, and for the same reason, variables should be very carefully used. Let us see very a quick example below that demonstrates that there transactions do not affect the local variable.

Now let us carry out the same test on TempTable and Table Variables. If Table Variables are true variables, they should also demonstrate the same behavior. See the following example:

USE AdventureWorks
GO
-- Create Temp Table and insert single row
CREATE TABLE #TempTable (Col1 VARCHAR(100))
INSERT INTO #TempTable (Col1)
VALUES('Temp Table - Outside Tran');
-- Create Table Variable and insert single row
DECLARE @TableVar TABLE(Col1 VARCHAR(100))
INSERT INTO @TableVar (Col1)
VALUES('Table Var - Outside Tran');
-- Check the Values in tables
SELECT Col1 AS TempTable_BeforeTransaction
FROM #TempTable;
SELECT Col1 AS TableVar_BeforeTransaction
FROM @TableVar;
/*
Insert additional row in trans
Rollback Transaction at the end
*/
BEGIN TRAN
-- Insert single row
INSERT INTO #TempTable (Col1)
VALUES('Temp Table - Inside Tran');
-- Insert single row
INSERT INTO @TableVar (Col1)
VALUES('Table Var - Inside Tran');
ROLLBACK
-- Check the Values in tables
SELECT Col1 AS TempTable_AfterTransaction
FROM #TempTable;
SELECT Col1 AS TableVar_AfterTransaction
FROM @TableVar;
GO
-- Clean up
DROP TABLE #TempTable
GO

It is clear from example that just like any local variable table variable is not affected from transaction. This is very important detail to note as I have quite often seen developer using TempTable and TableVariables interchangeably without understanding their effect on transaction.

Let me know if you have any other tip which you think will be helpful to readers.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs  that are associated with Transactions.

1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.

2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the current session.

Now, what really interests me is the following observation. These two DMVs , in actual fact, display the distinction between active transactions and current transactions. Current transaction can be active transaction at the time of execution, but not all active transactions are current transactions for current transactions DMV.

Let us take a look at an example where first we will run both these DMVs together in a single transaction and then run them separately.   We will notice that in the former case (i.e., when they are run in a single transaction) both of them give same transaction ID in result. While in the latter case (i.e., when they are run separately) they give different transaction IDs in result.

SELECT * FROM sys.dm_tran_active_transactions
SELECT * FROM sys.dm_tran_current_transaction
/* Begin Transation */
BEGIN TRANSACTION
SELECT
* FROM sys.dm_tran_active_transactions
SELECT * FROM sys.dm_tran_current_transaction
COMMIT
/* End Transation */

Let us see the following result image and observe the behaviour we discussed above.

To obtain same transaction ID, both the DMVs should be between BEGIN TRANSACTION and END TRANSACTION. Same transaction ID is preserved between BEGIN and END clause of transaction.

Having said all that, now I am eager to receive answer to this question from my blog readers – In what circumstances do you think this behaviour can be useful?

If possible, please write an article on a real life scenario and send it to me.  I will be vary happy to publish it on this blog.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks

Loyal reader of this blog and “Great SQL Expert” Imran Mohammed always have good attitude towards any problem. Many times his answers very interesting to read and details are very accurate. I came across his two interesting comment on this blog and I would like to share this all of you.

Priyank asked following question.

Can u tell us something about how to find which sql table is having the lock and of what type. also please tell us how to remove a lock from a locked table
thanks
Priyank

Imran Mohammed answered in great depth to this question. I personally enjoyed it very much.

@Priyank

In SQL Server 2000 (Enterprise Manager)

1. Expand server – management-currentActivity-expand
locks/processid and you will be able to see all the locks related information.

2.Expand server – management-currentActivity-expand Locks/object you can see locks by object information.

In SQL Server 2005 (SSMS, object Explorer)
Expand-server-management-double click Activity Monitor.
on left side you have three options to choose from, select those options and you can see all the locks related information.

run this stored procedure in the database.

1. sp_lock

to know the running process in the sql server, run this query,

2. select * from sysprocesses ( in sql server 2000)
3. select * from sys.sysprocesses ( in sql server 2005)

4. sp_who
5. sp_who2 will also give you some good information.

To work around the locks, you can run profiler to check which query is is creating a lock and if that is necessary.

Types of locks on object level, ( general idea)

Database : Database.
Extent : Contiguous group of eight data pages or index pages.
Key: Row lock within an index.
Page: 8-kilobyte (KB) data page or index page.
RID :Row ID. Used to lock a single row within a table.
Table: Entire table, including all data and indexes.

Types of locks;
Shared (S) – more than one Query can access the object.
Exclusive lock (X) – only one Query can access the object.
Update lock (U)
Intent share (IS)
Intent Exclusive (IX)

Just to give you a brief idea about locks, We have something called as transaction levels in sql server databases.

TRANSACTION ISOLATION LEVEL
level 0. READ COMMITTED
level 1. READ UNCOMMITTED
level 2. REPEATABLE READ
level 3. SERIALIZABLE

level 0 is the lowest level isloation level, if your database is set in this isolation level, no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This data will also read uncommitted data. Data which you have not comitted, you can still read that data.

level1 is the default isolation level of the database.
Under this category you will not be able to read uncomitted data, this is also called as dirty data. Under this we will have shared locks.

As the level increases the locks also increases. The highest is the serializable.

To make you understand in detail, lets see an example of what is committed data and what is uncomitted data.

use pubs
create table example1 ( eid int, ename varchar(10))

begin tran T1
insert into example1 values ( 1, ‘example’)
go

select * from example1 — this is uncomitted data.

The above is uncomitted transaction, because you started the transaction with a begin, you have to commit the transaction, untill then the transaction will not be uncommitted.

to commit the same transaction

commit tran T1

select * from example1 — this is committed data.

To check what is the current isolation level of your database, run this command,

Dbcc useroptions — check for isolation level.

If you dont want your query to put locks on objects you might want to use something like this,

select * from example1_1 with (nolock)

This will not keep any lock, not even a shared lock on the table.

This is indepth concept try looking BOL.

Hope this helps,
Imran.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 -Track Down Active Transactions Using T-SQL

Just a day ago, I was wondering how many active transaction are currently in my database. I found following DMV very useful – very simple and to the point.

Following SQL will return currently active transaction.

SELECT *
FROM
sys.dm_tran_session_transactions

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS

Few days ago, I was searching MSDN and I stumbled upon following two global variables. Following variables are very briefly explained in the BOL. I have taken their definition from BOL and modified BOL example to displayed both the global variable together.

@@CONNECTIONS
Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.

@@MAX_CONNECTIONS
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.

@@MAX_CONNECTIONS is the maximum number of connections allowed simultaneously to the server. @@CONNECTIONS is incremented with each login attempt, therefore @@CONNECTIONS can be greater than @@MAX_CONNECTIONS.

Example:
SELECT GETDATE() AS 'Currunt Time',
@@CONNECTIONS AS 'Total Logins so far',
@@MAX_CONNECTIONS AS 'Max Connection Simultaneously'

Currunt Time Total Logins so far Max Connection Simultaneously
———————– ——————- —————————–
2007-09-03 17:28:12.013 71 32767

Reference : Pinal Dave (http://blog.SQLAuthority.com)