SQL SERVER – Error – Msg 6401, Level 16: Cannot roll back Transaction

In a recent Usergroup meet that I attended someone asked me this simple yet interesting question about how transactions work and is there any concept like nested transactions inside SQL Server. Though I have talked about this in the past, thought it would be worth revisiting this concept to drive home some learnings again to readers who are new to SQL Server. Let us learn this using the script code as shown below:

-- 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

Once the database is created, our next logical step is to create some transactions to test if we can mimic the use of nested transactions.

-- Create an explicit transaction
BEGIN TRAN OuterTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (1);
GO 10
-- Create an explicit so called nested transaction
BEGIN TRAN InnerTransaction;
GO
INSERT INTO tbl_SQLAuth VALUES (2);
GO 10
-- Look at the trancount?
SELECT @@TRANCOUNT;
GO

In the example above we started using an explicit transaction called as “OuterTransaction” and then inserted values post which we created yet another transaction called as “InnerTransaction”. The @@TRANCOUNT will show a value of 2 now. Now that we know the transaction count is 2, let us try to rollback the inner transaction alone.

-- Rollback the inner transaction
ROLLBACK TRAN InnerTransaction;
GO

Msg 6401, Level 16, State 1, Line 33
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.
Now this is interesting to see the error, we have explicitly created a transaction called as “InnerTransaction” in our code and it is getting ignored.

-- Works?
ROLLBACK TRAN OuterTransaction;
GO

Or the below command works:

-- Works?
ROLLBACK TRAN;
GO

And it is so strange that this above commands worked. If you try to query the table, it will have no rows.

-- What hapenned?
SELECT COUNT(*) FROM tbl_SQLAuth;

This is an important learning one needs to know when working with ROLLBACK command inside SQL Server. There is nothing like a nested transaction perse inside SQL Server. Go ahead and do the clean up.

-- Cleanup time
USE MASTER;
GO
DROP DATABASE TransactionsDemo;
GO

I am curious to know from developers out there, have you ever faced this situation in your environment and learnt it the hard way? If you can share your learning, it would be great.

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

SQL SERVER – FIX – A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)

Life gives you learning lessons from unexpected times, here is one such experience that I felt is worth sharing. It may be a surprise to my blog readers that I also have SQL Server Analysis Services installed on my primary machine. I use it occasionally because most of the time I work with the SQL Server Database engine. One fine day, I opened SQL Server Management Studio and instead of database engine, I selected analysis services (this was a mistake) and pressed on the connect button. Below is the error which I received:

TITLE: Connect to Server
——————————
Cannot connect to BIGPINAL
——————————
ADDITIONAL INFORMATION:
A connection cannot be made to redirector. Ensure that ‘SQL Browser’ service is running. (Microsoft.AnalysisServices.AdomdClient)
——————————
No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)
——————————
BUTTONS:
OK
——————————

This is the first time I saw such error while connecting to SQL Server, but soon I realized that I was trying to connect to SQL Server Analysis Services. I could have left it there because I don’t use it, but I did not. Since there was a little time, I thought it is worth solving this challenge in front of me. Here is the step by step resolution of what I did:

  1. Made sure that SQL Server Analysis Service was up and running. This got confirmed from my SQL Server Configuration Manager.
  2. As I do always, read the error message again and verified that the SQL Browser service was up and running.
  3. Since this was a local connection, I turned off the firewall as well.

None of the above solved the problem. The Port resolution is done by the SQL browser service in case of SQL Server connectivity. So I thought that it could be a permission issues – just a guess based on the error message.

I noticed that SQL Browser was running with the local system account. I found on internet that changing account helped many. So I used SQL Server Configuration Manager to change service account for the SQL Server Browser. I have used like below.

Here are the steps:

  • Open SQL Server Configuration Manager
  • Right click “SQL Server Browser” and choose “Properties”
  • In the “Log On” Tab select “Local System” under “Built-In” account.

Have you ever seen such error and found some other way to fix it? Please comment and let others also know. The best way to learn something is by sharing.

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

SQL SERVER – How to Add User Across Databases

In this era of standards, many organizations want to be using some of the cutting edge technologies in their organizations. In many of these interactions I was lucky enough to meet few DBA’s who give me interesting challenges to meet within their organizations. I still vividly remember sharing a script with a DBA who reached out to me to know how we can add a user to all the databases within their SQL Server instance.

On first look, I asked “why?” The answer was simple. Their organization data gets audited once a year by the auditors and they need to be given db_reader privileges during the time of audit. So they wanted to make sure this is done in an automated fashion. I was immediate to give them recommendation to my Plurasight Course. But they were not on the latest version and wanted something in previous versions.

So here is the script that I gave them:

EXEC sp_MSforeachdb '
DECLARE @name VARCHAR(500)
SELECT @name = ''?''
IF ''?'' not in (''tempdb'')
BEGIN
USE [?]
IF DATABASEPROPERTYEX(''?'',''Status'') =''ONLINE'' AND DATABASEPROPERTYEX(''?'',''Updateability'')=''READ_WRITE''
BEGIN
IF NOT EXISTS(Select * from sys.sysusers where name = ''SQLAuth_Auditor'')
BEGIN
CREATE USER [SQLAuth_Auditor] FOR LOGIN [SQLAuth_Auditor]
PRINT ''Added User for ?''
END
EXEC sp_addrolemember ''db_datareader'', ''SQLAuth_Auditor''
PRINT ''Added db_datareader for ?''
END
ELSE
PRINT ''SKIPPED the ? database''
END '

The script uses an undocumented command sp_msforeachdb which I have used before in earlier blogs too to achieve some interesting requirements.

Here in the script, we check of SQLAuth_Auditor user existence in all the databases. If it is not available, we go ahead and add the db_datareader role to the database.

My ask: How many of you have used sp_MSforeachdb procedure in your environment? What are the use cases where you have successfully used the same.

Quick Quiz:

To achieve the above task, can’t we just add the user to Model database and all databases created will automatically get the user added? Is there any catch provided there are no databases currently in a given instance? Do we still need a script like this? Let me know your thoughts.

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

Interview Question of the Week #010 – What is the Difference Between Primary Key Constraints and Unique Key Constraints?

I often see people getting confused between Primary Key and Unique Key. I see where people get confused about the same. Let us discuss.

Question: “What is the Difference Between Primary Key Constraints and Unique Key Constraints?”

Answer: “Primary Key (PK) Constraints enforce uniqueness just like Unique Key (UK) Constraints, however, PK does not allow  NULL value where as UK allows one NULL value.”

Well, the answer is as simple as above statement. You can read more about this over here: Primary Key Constraints and Unique Key Constraints.

I am very sure after reading above statement there will be few readers, who is going to comment and write email that when we create Primary Key Constraint it also creates clustered index on that column but Unique Key Constraint does not create clustered index. Well, this is the specific (and default) behavior in SQL Server where clustered index is automatically created, however user can create Primary Key Constraint without creating a clustered index as well and you can read about this over here Primary Key and NonClustered Index in Simple Words.

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

SQL SERVER – Using Buffer Pool Extension with In-Memory OLTP?

I am always amazed to see the questions asked by my blog reader which lead me to learn more about SQL Server.  Here is the question which took me little time to figure out the answer.

If you are new to SQL Server 2014 new features which I have in subject, I would recommend you to go through below blogs first.

SQL SERVER – Beginning In-Memory OLTP with Sample Example
Buffer Pool Extension in SQL Server 2014 Pluralsight Course

The blog reader asked me below question:

Hi Pinal,
I watched your Pluralsight course and was able to easily learned about two new features, In-Memory OLTP (Hekaton) and Buffer Pool Extension (BPE). Thanks for making such course. I have some follow-up question and I hope to get answer from you.

My In-Memory table would be 10 GB in size. As per your course, I would need at least 10 GB of RAM so that complete table can fit in memory. Can I use buffer pool extension and live with 5 GB RAM and 50 GB BPE file?

Regards,
<Name Hidden>

This is indeed an interesting question. If we go back to basics, we need to keep below in mind.

  1. In-Memory OLTP doesn’t uses Pages. It just has free flowing rows in memory, tied to each other via index.
  2. Buffer Pool is the area where SQL Server keeps every page, query plan.
  3. Buffer Pool Extension feature is to extend the buffer pool.

If we tie all three points together, we can easily conclude that Buffer Pool Extension cannot be used to hold data belonging to In-Memory tables.

Here is the quick image which can show what would happen if In-Memory table size grow

Hope this would help you in remembering that In-Memory Tables are not part of Buffer Pool and hence we can’t extended the data from those tables to extension file.

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

SQL SERVER – ReadOnly Databases with ReadOnly File Attribute

As part of continued series of ReadOny databases, the earlier blog had an interesting comment that read as some people were getting errors of 5120 and others. This got me curious and was in the pending list of solutions in some way.

One of the readers had mailed me saying, they attached a database and the databases came up as ReadOnly. They didn’t know why this happened and when they try to mark the database as ReadWrite, it was giving them an error.

Here are the steps to reproduce the situation. I have the database files in a fileshare. We will try to attach the same using the below command:

USE [master]
GO
CREATE DATABASE [ReadOnlyDB] ON
( FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB.mdf'),
(
FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB_log.ldf')
FOR ATTACH
GO

Note: In your case, you can have database files on a different folder.

As soon as the command is executed, we can see in the object explorer the following:

We can see that the database is marked as Read-Only on attach. This was strange. So based on the blog, let us try to make the database as ReadWrite using the following command:

USE [master]
GO
ALTER DATABASE [ReadOnlyDB] SET  READ_WRITE WITH NO_WAIT
GO

Now this raised the following error:

Msg 5120, Level 16, State 101, Line 36
Unable to open the physical file “C:\ReadOnlyDB\ReadOnlyDB.mdf”. Operating system error 5: “5(Access is denied.)”.
Msg 5181, Level 16, State 5, Line 36
Could not restart database “ReadOnlyDB”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 36
ALTER DATABASE statement failed.

The catch to the error is hidden in the message. It says “Operating system error”. So let us go ahead and detach the DB to change the OS File attributes.

USE [master]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'ReadOnlyDB'
GO

Next get to the File system attribute for the database and Log files to check for the ReadOnly attribute. In our example, the mdf file had this attribute enabled.

Please go ahead and uncheck the “Read-only” attribute from the file. That is it. Now attach the database after the change.

USE [master]
GO
CREATE DATABASE [ReadOnlyDB] ON
( FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB.mdf'),
(
FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB_log.ldf')
FOR ATTACH
GO

This got us back to normal life as far as our ReadOnly databases is concerned. Hope you learnt something new as I am learning something new almost every single day. Do let me know if you got a chance to learn so much working with ReadOnly databases in the past.

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

SQL SERVER – Using Raw Files in SSIS – Notes from the Field #070

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Using Raw Files in SSIS.

Linchpin People are database coaches and wellness experts for a data driven world. In this 70th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to use raw files in SSIS.


SQL Server Integration Services is well designed for retrieving and processing data on the fly, directly in the data flow pipeline. However, there are circumstances that occasionally require the persistence of result sets in SSIS for use during package execution. For these such cases, one option is to use SSIS raw files.

The raw file in SSIS is a special kind of binary file, optimized for and intended for use only through SSIS. This special file is intended to store temporary result sets on disk to allow reuse of data across multiple packages and multiple executions.

Although these are not tools one would likely use on a daily basis, the raw file tools in SSIS are helpful in a number of scenarios:

  • The same result set is reused several times as part of the different data flow (or even different packages)
  • Due to size or query complexity, retrieving a particular set of reference data takes a significant amount of time
  • Due to business rules, one or more interim result set is required during the ETL process

In cases such as this, using raw files to store result sets can be a handy solution.

To create and use raw files, SSIS comes with two components – a raw file source and a raw file destination – to allow reading from and writing to these files. I’ll start by showing the destination (ordinarily I would start with the source, but since we need to create a raw file with the destination component before we can use the source component, I’m changing the typical demonstration order). As shown below, this example uses a data flow with an OleDB source – connected to AdventureWorks – for sending sales data into a raw file destination.

On the raw file destination, we’ve got a handful of configuration options, including:

  • The type of file name (either direct or from an SSIS variable)
  • A selector to choose the file name or the variable supplying that file name
  • An option to select the write method (create, overwrite, append, etc.)
  • A column pane to allow source-to-target mappings

Note that on the Write Option selection, there are some limitations with respect to whether the specified output file already exists. For example, if you choose the Create Once option, you’ll see a design-time error if the file already exists. Similarly, if you choose either the Append or the Truncate and Append option, you’ll see an error if the file does not yet exist. To make the design-time experience easier, there is a button in the UI of the destination to create the initial raw file in case you want to use either of the Append options without having previously created the file.

The raw file source, the simpler of the two raw file components, has just a couple of configuration options: the type of file name, a selector to choose the file name or the variable supplying that file name, and a data grid to allow the selection of the columns to be included from the raw file.

As shown in use below, the raw file source feeds the data flow pipeline, ultimately sending its data into the OleDB destination.

Final thoughts

One thing you’ll want to keep in mind is that there is no automatic purge process to empty or remove the raw files after use. Therefore, if there is a concern about persisting the files, either because of disk space or data sensitivity, there should be a follow-on process that truncates or deletes the raw files after they are no longer needed. Also, if you want to track how recently the raw file was loaded, you could either check the update stamp of the file (using a script task), or use a date stamp column in the process that loads the raw file to allow storage of the load date in the data within the file.

Conclusion

The raw file source and destination in SSIS can be used to address situations where temporary file system storage of result sets is required in an ETL process. Although not an everyday set of tools, this source and destination are useful tools to know how to use in cases where persisting interim result sets is needed.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

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