SQL SERVER – FIX: Msg 3102, Level 16, State 1 – RESTORE cannot process database ‘Name’ because it is in use by this session

Some of the simple learnings can come from unexpected places and this blog post is a classic example to that statement. Recently I got an email from one of my readers about the below error message:

Msg 3102, Level 16, State 1, Line 2
RESTORE cannot process database 'DBProductDevelopment' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

As I always do, I searched on my own blog using http://search.sqlauthority.com and found few earlier blogs where few of my readers reported the error which is the source of this blog.

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

SQL SERVER – FIX : ERROR : Msg 3159, Level 16, State 1, Line 1 – Msg 3013, Level 16, State 1, Line 1

One of my reader has asked interesting question related to this error:

Does it mean that I need to restore master database first and then restore this? How is that possible? Is there something wrong with error message?

Answer: No, the error message is not asking to restore master. All it is saying is that the restore command is running in the same database for which restore is being attempted. Error message is asking to change context to master database before performing restore.

Here is the repro of the problem:

USE SQLAuthority
BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuthority.bak'

restore error 01 SQL SERVER   FIX: Msg 3102, Level 16, State 1   RESTORE cannot process database Name because it is in use by this session

As we can see, SQL Server can’t complete the restore operation because the user database has an active connection within our current session. We’ll need to change our database connection to a different database before we attempt to perform the restore. We can use the T-SQL USE command:

Here is the modified script where I have highlighted the change.

Here is the cleanup script.

restore error 02 SQL SERVER   FIX: Msg 3102, Level 16, State 1   RESTORE cannot process database Name because it is in use by this session

/* clean up*/

Have you ever face similar error in your environments? Do you have any similar experience to share? Let me know over the comments section below.

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

SQL SERVER – FIX – Msg 3702, Level 16, State 3 – Cannot Drop Database “DB_Name” Because it is Currently in Use

We learn from mistakes and we improvise with experience. I couldn’t think of a better example than the one below for this. I have been watching many sessions by various speakers and found that many times they have struggled to get rid of below error message.

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “DemoDB” because it is currently in use.

The meaning of the error message is pretty clear that someone is using the database and it can’t be deleted/dropped. First, make sure that it is not our own connection. To make sure, always change the context before dropping the database as shown below

kill 01 SQL SERVER – FIX – Msg 3702, Level 16, State 3   Cannot Drop Database DB Name Because it is Currently in Use


If we are still getting above error, then there are two approaches to solve this:

  1. Long approach: Find the connections which are blocking me to drop the database. Essentially find those sessions which are using this database and then kill those sessions. Here is the script to generate kill command.

SELECT 'kill ' + CONVERT(VARCHAR(100), session_id)
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('DemoDB')
session_id <> @@spid

This would give output as below.

We can run the kill command and try dropping database again. If it was successful, go ahead else repeat the loop.

  1. Short approach: SQL Server has inbuilt functionality where it can kick out all the connections. There is a cause called “WITH ROLLLBACK IMMEDIATE” which can be used to kill other connections and forcing rollback of their work.

USE [master]
USE [master]

If you want to learn other rollback options available with ALTER DATABASE, you can read my earlier blog as well


Hope this helps and you will use the new technique in your presentations.

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

SQL SERVER – Basic Statistics Maintenance – Notes from the Field #083

[Note from Pinal]: This is a 83rd episode of Notes from the Field series. Maintenance of the database is very critical activity and I have always seen DBA taking it very seriously. There is a only one problem – there is no single solution or guidance for how to maintain the database. Everybody has their own opinion and way to do different tasks. Statistics is one of the most important aspect of the database. The performance of entire application can depend on statistics, as it can help SQL Engine with intelligence to execute optimal plan.

In this episode of the Notes from the Field series database expert Stuart Ainsworth explains about basic statistics maintenance.

 SQL SERVER   Basic Statistics Maintenance   Notes from the Field #083

Statistic maintenance is an important (but often overlooked) aspect of performance tuning for SQL Server.  The query optimizer relies on distribution statistics to determine how the query will be executed, with a particular emphasis on SEEKS vs SCANS as well as estimates of effort needed (the cost threshold for parallelism).  Out-of date stats can impact performance significantly.

Luckily, the default setting for most databases covers most database performance scenarios; SQL Server offers three basic settings for statistics maintenance:

  • Auto Create Statistics – SQL Server will create statistics during an index creation script, or when deemed necessary to satisfy a query; enabled by default.
  • Auto Update Statistics – SQL Server will update statistics when it deems them to be outdated; enabled by default.
  • Auto Update Statistics Asynchronously – When enabled, SQL Server will updated statistics after the execution of a query if it determines that an update is necessary; if disabled, the statistics will be updated first, and then the query executed. This setting is disabled by default, and there’s some controversy over whether or not it’s useful.

notes 83 1 SQL SERVER   Basic Statistics Maintenance   Notes from the Field #083

For the most part, SQL Server does a good job of maintaining statistics if these options are left with the defaults; however, statistics can still become stale over time if the data is updated at a slow rate of change.  If your statistics are more than a year old in your database, it’s probably time to do a refresh.

But how do you tell when your stats are out of date? There’s a catalog view in SQL Server called sys.stats that will give you a lot of information about statistics, but it’s very detailed; data is collected down to the column level, which may be overwhelming if you have lots of databases.  I suggest starting at a higher level, by taking a look at how out of date statistics are across all of your databases.   I use the following query to help me quickly identify troublesome databases:

/*checks last updated stats date for all databases; calculates a percentage of stats updated within the last 24 hours.
useful to determine how out-of-date statistics are.  Also identifies if auto updatestates are on and if asynchronous updates
are enabled.
database_name VARCHAR(100)
oldest_stats DATETIME
, newest_stats DATETIME
, percent_update_within_last_day DECIMAL(5, 2)
is_auto_update_stats_on BIT
, is_auto_update_stats_async_on BIT
DECLARE @dynsql NVARCHAR(4000) = 'use ?
if db_id() >4
WITH    x AS ( SELECT   STATS_DATE(s.object_id, stats_id) date_updated
FROM     sys.stats s
JOIN sys.tables t ON t.object_id = s.object_id
WHERE    t.is_ms_shipped = 0
) ,
AS ( SELECT   MIN(date_updated) AS oldest_stats
, MAX(date_updated) AS newest_stats
FROM     x
SELECT DB_NAME() database_name
, oldest_stats
, newest_stats
, SUM(CASE WHEN DATEDIFF(d, date_updated, newest_stats) <=1 THEN 100.00 ELSE 0.00 END)/COUNT(*) "percent_update_within_last_day"
, d.is_auto_update_stats_on
, d.is_auto_update_stats_async_on
FROM    x
CROSS JOIN sys.databases d
WHERE d.database_id = db_id()
GROUP BY oldest_stats
, newest_stats
, d.is_auto_update_stats_on
, d.is_auto_update_stats_async_on
( database_name
, oldest_stats
, newest_stats
, percent_update_within_last_day
, is_auto_update_stats_on
, is_auto_update_stats_async_on
EXEC sp_MSforeachdb @dynsql
FROM    #dbs d

Results will look similar to the following:

notes 83 2 SQL SERVER   Basic Statistics Maintenance   Notes from the Field #083

Looking at the results, if I see that the oldest database stats are more than a year old, it’s s a pretty good indicator that statistics are not being maintained by some form of ongoing maintenance operation.  If the defaults are not being used, that’s also something that needs to be investigated.

The percent_update_within_last_day is also a good trigger for me to investigate a potential issue; if less than 20% of the statistics in a database were updated in the last 24 hours, the odds of a less-than-optimal execution plan increase significantly.  If the tables in the database are large, it may take several million rows of changed data to trigger a refresh.

To refresh outdated statistics, I recommend Ola Hallengren’s maintenance scripts; they offer a lot of flexibility for developing a customized index and statistics maintenance plan, including the ability to update statistics on a scheduled basis, and focus on updating statistics that need to be refreshed.  Basic maintenance (such as this) can help prevent performance problems from occurring.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

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

SQL SERVER – Finding Out Identity Column Without Using Column Name

There is an interesting observation when querying the identity column of a table

Let us create the following tables

id INT

Note that the table test1 has identity column and table test2 does not have it

Now you can find the values of identiy column without using actual column name

SELECT $identity FROM test1

You will not get an error and look at the column name. It is id which is the identity column of test1.
The quick way to check if the table has identity column and it it has, to know the column name use the following

SELECT $identity FROM test1 WHERE 1=0

If the table does not have identity column you will get an error

SELECT $identity FROM test2 WHERE 1=0

The error is

Msg 207, Level 16, State 1, Line 1
 Invalid column name '$identity'.

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

SQL SERVER – Errors and Limitations of Working with SPARSE Columns in SQL Server

I make a visit to the local user group whenever I get and keeping the community juices up and running for me. I get an opportunity to share some of the learnings and meet tons of new people. In our recent meetup at the SQLBangalore UG, I met a lot of young talent joining the IT field. Many take time to walk up to me and share a moment now and then. It is in this continued journey that inspired me to write this blog.

When I was at the UG meet, I said I write on a lot of topics and showed up this blog. It is rarely that someone fails to stumble onto this space. But one kid walked up to me and asked, what is SPARSE columns? After I explained what it was, he immediately asked me – is there any catch to it? Are there restrictions and limitations? Though there were few that I mentioned, I thought I will write few as part of this blog.

NULL Effect

Sparse columns MUST be nullable. Trying to create a sparse column as not NULL will fail with an error.


Msg 1731, Level 16, State 1, Line 1

Cannot create the sparse column ‘Name’ in the table ‘noNullSparse’ because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.

Though the error is not explicit, you can see that the columns marked as SPARSE cannot be NOT NULL fields.

Datatypes to Watch

The above error gives away a lot of information on what are the datatypes that will cause you possible errors if marked as SPARSE.

  • geography
  • geometry
  • image
  • ntext
  • text
  • timestamp
  • user-defineddatatypes(UDT)

The other attributes like IDENTITY, FILESTREAMS and ROWGUIDs are also not allowed.

SPARSE with Computed Column

Lesser known is that SPARSE column’s cannot be used with Computed Columns. But, we can use a SPARSE a column inside a Computed Column. Below is a classic example:

-- Trying to mark a computed column as SPARSE will fail with incorrect syntax error
-- However, including an existing SPARSE column in a computed column is allowed
-- Cleanup

Final Note

The other times we are likely to get an error is to mark a SPARSE column with default values, Primary key, clustered Index, partition key, user defined table type etc. These are some of the restrictions when working with SPARSE columns and will raise an error if used.

Would be great to know if anyone uses SPARSE columns in your designs anywhere? What are the scenario’s you found it useful? Let me know via your comments as it would be a great learning for all.

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

SQL SERVER – Restore Error: Specified cast is not valid (SqlManagerUI)

As I always say, Errors are a great source of learning and I am fortunate to have you as my readers. Because one of my blog reader reported an interesting issue which is worth blogging. This is his initial email:

Hi Pinal,
Your blogs have been very helpful to me to find solution of almost any SQL problem so far. This time, I am writing directly to see if you have time and provide some help to me.
I have a backup from database in SQL Server 2008 R2. When I want to restore this backup to SQL Server, I get this error: “Error: Specified cast is not valid. (SqlManagerUI)” How to I resolve this error?

We then exchanged various mails with screenshots, query, output and with his patience, we were able to find the cause of the issue. Here we go to what happened behind the scenes:

Here were the steps to restore the database:

cast error 01 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

As soon as OK was clicked, this was the error.

cast error 02 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

If we click on red cross icon at left bottom, we would see below

cast error 03 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

Here is the partial text of the message.

Specified cast is not valid. (SqlManagerUI)
Program Location:
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.PopulateGridWithBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.GetBackupSetsFromDevices()
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseGeneral.textDeviceSelected_TextChanged(Object sender, EventArgs e)

As we can see above that SSMS is trying to populate the grid with the details about the backup. When I put profiler on my machine, it was running Restore Headeronly command on the selected file. So, I asked to run the command manually. You can refer my earlier blog for this SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

When we ran the command, we found below

cast error 04 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

Above could be because of two reasons (which I found)

  1. Backup taken on SQL 2012 and Restore Headeronly was done in SQL 2008 R2
  2. Backup media is corrupted.

Solution of 1st one is easy – restore it on same or higher version. 2nd one is a difficult situation because you need to look for another good/restorable backup. In my case, it was first situation so we were good.

Another possible reason of “Specified cast is not valid” would be password protected backups. In such backups, headeronly would return “*** PASSWORD PROTECTED ***” in the first column as shown below.

cast error 05 SQL SERVER   Restore Error: Specified cast is not valid (SqlManagerUI)

So, next time you ever see any UI error, find the T-SQL statement by using profiler and run that directly to see “real” error message.

Have you ever come across situation where UI was giving some misleading error? Please comment and share your knowledge.

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

Interview Question of the Week #022 – How to Get Started with Big Data?

Big data is one of the most popular subject in recent time and everybody wants to get started on this subject. During recent interviews there are plenty of the questions with related to Big Data. Here is the most popular question which I receive on this subject.

Question: How to get started with Big Data?

Answer: Earlier last year I wrote timeless series on the subject Big Data. Here is the link to the entire series.

 bigdataimages Interview Question of the Week #022   How to Get Started with Big Data?

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

SQL SERVER – Error Msg 511 using SQL Server Sparse columns

When I wrote the previous post around SQL SERVER – CREATE TABLE Failed Because Column ‘C1024′ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns, I thought it was a simple concept. Lesser did I know that there can be something wrong. One of my blog reader said they were getting some sort of an error when adding a sparse column to a table. I was surprised to why one should get an error? I asked for further details because this was strange.

The mail stated they are getting:

Msg 511, Level 16, State 1, Line 6
Cannot create a row of size 10019 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Just like a strange error gets me going. I started to dig into why are they were getting this error. This lead to an interesting learning for me with sparse columns.

Using sparse columns reduces the maximum size of a row from 8060 bytes to 8018 bytes because of the additional overhead used when storing non-NULL values. However, for most of the normal scenarios this should not be a concern, since by definition, most of the sparse columns in a row will contain NULL values. Therefore, size reduction obtained by not storing any data for NULL values in a sparse column easily compensates for this fixed overhead. This still doesn’t tell us why we are getting the above error. Let us understand the basics.

When you convert a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. Therefore, when the row size is near 4,009 bytes, the operation can fail. The reason is when we modify an existing table to add our space column, the data pages need to have enough space to make a copy of the original row, make changes and then delete the original row. The error is because of insufficient space on the data pages. Let us do a simple script to get the error:

--Create a column of size 6000 (note this is NCHAR)
CREATE TABLE sparse_tbl (c1 NCHAR(3000))
INSERT INTO sparse_tbl VALUES (REPLICATE ('z',100))
--Modify the column to sparse

msg511 sparse 01 SQL SERVER   Error Msg 511 using SQL Server Sparse columns

A similar error is encountered if you go ahead and remove a sparse column from an existing table with data where the rows have more than 4009 bytes. In case of a new table, this is not encountered in general because there is no data to work with or move.

So the next question was, how can I alter or add or remove sparse columns which have data more than 4009 bytes then? In this case:

  • Create a new table for the operation
  • Insert into new table using the old table
  • Delete the old table and rename the new table

If we follow a process to do the same, there is nothing difficult. I have always felt there are ways to work with data and work around things. I am hope in future versions Microsoft might change this behavior but this is the current workaround. Hope you have never encountered this error before in your code. Let me know your thoughts.

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

SQL SERVER – Using 20 Logical Processors Based on SQL Server Licensing

It is second to human nature to utilize every bit of juice for what we pay. You buy something and you would like to get the best out of it right? If you start thinking from an organizations point of view, they think they should buy something which satisfies their needs. That is the only reason why organizations do capacity planning for their applications. Hardware resources are not cheap and it is difficult to over budget on them. Below is a real world query that had come to me.

One of my friend contacted me and told that he is using task manager and seeing that out of 64 processor only 20 processors are shown as utilized for a SQL Server machine. Whenever someone contacts me for any issue, I always want to know their environments details and ERRORLOG gives me a fair idea about it. If you don’t know where ERRORLOG is then you may want to read my earlier blog.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

So, he sent me an ERRORLOG and I found below message over there.

SQL Server detected 8 sockets with 8 cores per socket and 8 logical processors per socket, 64 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

This explains the behavior which he is seeing but he also wanted to fix it. I went ahead and looked in the ERRORLOG’a again and found below on the top.

2015-05-13 11:00:54.72 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1)

This is an enterprise edition of SQL so I was expecting it to use all processors and full power of the machine. I searched licensing guide and found that from SQL Server 2012 onwards, there are two enterprise licenses – Core Based and SERVER/CAL Based. As per my research, CAL based license limits SQL Enterprise to use only 20 physical processors. If hyper-threading is enabled then it would be 40 logical processors.

We have identified that this behavior was correct as per license they have purchased. Since this was their most critical server, they have purchased core based license. They came back to me and asked – “How can we change the installed SQL Server to use Core Based license?”

Answer was simple – perform the edition upgrade using new media Here are the steps:

  • Launch the setup.exe from your setup media or network location.
  • Select Maintenance on the left pane of the setup dialog.
  • Select Edition Upgrade on the right pane.
  • After the standard pre-requisite check you will get a Product Key Page. You need to enter your new product key.

Once complete, the Errorlog showed below.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1)

If you are using SQL 2012 onwards and having big machine, I would like you to check the ERROLROG and check if you are running into same issue. Else you can also run


Edition 01 SQL SERVER   Using 20 Logical Processors Based on SQL Server Licensing

This is mostly observed when there is an upgrade from earlier version of SQL Server because you might not be aware of that. Check and comment if this blog has helped you.

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

SQL SERVER – What are T-SQL Window Functions? – Notes from the Field #082

Kathi SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about easy and amazing functions of SQL Server. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.

The first set of T-SQL window functions have been available for 10 years, but many SQL Server professionals are not quite sure what window functions are. I am frequently asked by people I meet at conferences and customer sites “What are window functions?” I always answer with another question, “Do you use ROW_NUMBER?” Almost every time, the answer is “Yes. I didn’t know that was a window function.”

T-SQL window functions are different than other T-SQL functions because they operate over a set, or window, of rows. Notice that I am not capitalizing “window.” They have nothing to do with the Windows operating system. They are part of the ANSI SQL 2003 standard.

T-SQL window functions give you the ability to perform a calculation over the rows that are the result of the FROM, WHERE, GROUP BY and HAVING clauses. You can do things like include a column from a different row, calculate running totals, and perform summary calculations without losing details or grouping.

My favorite T-SQL window function is called LAG. LAG, introduced with SQL Server 2012, lets you bring in a column from a previous row. This is useful for calculating year over year performance, for example.

Here is the syntax of the LAG function:

LAG(<expression>[,offset][,default]) OVER([PARTITION BY <expression>] ORDER BY <expression>)

You must provide the column name that you wish to bring forward, and you must provide an ORDER BY expression. The ORDER BY is used to determine just which row is the previous row. By default, the value returned is from the previous row. By specifying an offset, you can go back any number of rows. You can also specify a default value to replace any NULLs that are returned.

Run the following script to create and populate a table for testing LAG.

SalesAmt MONEY
CONSTRAINT PK_Sales PRIMARY KEY(SalesYear, SalesQtr));
INSERT INTO #Sales( SalesYear, SalesQtr, SalesAmt )
VALUES (2009,1,1400),(2009,2,2200),(2009,3,2800),(2009,4,1000),

The following query uses the LAG function with an offset value of four to return the sales from the same quarter of the previous year.

SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearQtrSales
FROM #Sales;

notes 82 1 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 1: The partial results of using LAG

To calculate year-over-year sales, you subtract the previous year’s sales from the current sales and divide by the previous sales. To make the calculation a bit simpler, I’ll add the query to a CTE and perform the calculation in the outer query.

WITH Sales AS (
SELECT SalesYear, SalesQtr, SalesAmt,
LAG(SalesAmt,4) OVER(ORDER BY SalesYear, SalesQtr) AS PrevYearSales
FROM #Sales)
SELECT SalesYear, SalesQtr, SalesAmt, Sales.PrevYearSales,
FORMAT((SalesAmt - PrevYearSales)/PrevYearSales,'P') AS YOY
FROM Sales;

notes 82 2 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082

Figure 2: Using LAG for year-over-year calculation

notes 82 3 SQL SERVER   What are T SQL Window Functions?   Notes from the Field #082The LAG function is really easy to use, and it performs great. If you would like to learn more about LAG and all of the other T-SQL window functions, be sure to check out my new book: Expert T-SQL Window Functions in SQL Server.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

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