Recently we encountered an error message as below while doing a rename database. So, let us look at the steps that we can use to overcome the issue with this post.
The database could not be exclusively locked to perform the operation.
We were trying to rename a database in one of our lower environment as below and ended up with the error message.
ALTER DATABASE dbname MODIFY NAME = dbname_new
Steps to resolve
Take the database to single_user mode
Rename the database
Take the renamed database to multi_user mode
Script
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname MODIFY NAME = dbname_new
GO
ALTER DATABASE dbname_new SET MULTI_USER WITH ROLLBACK IMMEDIATE
If you enjoyed this blog post, please share it with your friends!
Today, let us quickly try to understand an error message as below.
“Database name ‘tempdb’ ignored, referencing object in tempdb.”.
Many of us would have got this message (I do prefer to call this as a warning message, not error message), however, we would not have noticed as it is not causing any fatal results. With this post, I would like to share my thoughts and like to have your views on this as well.
Let us first see when would you get this message?
use tempdb
GO
create Table #Temp_Table(Col1 int)
GO
Select * From tempdb..#Temp_Table
Now, interesting thing, when the query is changed a bit as below, the message is vanished.
Somehow, I was not convinced the above method as a solution, because that was actually against my understanding of 4 part naming convention (servername.databasename.schemaname.objectname). So, we further tried to test with few more combinations to understand how it works internally.
A simple query as below showed #Temp_Table is created with dbo schema.
As next step, tried to create a schema and a new object is created. If you look at the schema of the object, we can clearly see that it is associated with dbo schema not the new schema. That means, the objects created in tempdb database is always creating in dbo, not in any other schema. In fact, the schema part is actually ignoring for temp tables.
create schema testschema
GO
create Table testschema.#Temp_Table10(Col1 int)
Points to ponder
There is NO need of specifying schema for objects created in tempdb database.
Even we specify a schema name, SQL Server simply ignores the schema part.
If you enjoyed this blog post, please share it with your friends!
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
As specified in the error message, its a rare event or exception. The important message from the exception is that “Please simplify the query”. When the query is complex, the relational engine would not be able to create a query plan due to various reasons like the environment resource limitation, reduced capacity etc. and end up with the above exception.
The case I observed is with a dynamic query execution. I do not exactly going to give the query, however tries to provide a more like scenario. I wanted to create a dynamic query with an IN condition where the list of items are separated with a comma like below.
declare @sql varchar(max)
Declare @values varchar(max)='val1','val2','val3'......'valhugenumber'
Select @sql = 'Select * from ' + @tablename + ' where ' + @columnname + ' in ('+@values+')'
Exec(@sql)
When executing this in one of environments (lower), the error is produced, however, the other interesting thing is its not happening in few other environments(eg. Production). As mentioned above, that is because it depends on the resources involved in various environment matters in producing the query plan.
Resolution:
From the analysis, the issue is associated with long (really long) list of values and when its parsing, the relational engine is not able to create a plan for the query execution. To resolve the issue, the query has been modified not to use the value list variable, instead populated those values into a temp table and then use the temp table as below.
declare @sql varchar(max)
create table #temp (valuelist varchar(100))
Insert into #temp values('val1'),('val2'),('val3'),......('valhugenumber')
Select @sql = 'Select * from ' + @tablename + ' where ' + @columnname + ' in (Select valuelist from #temp)'
Exec(@sql)
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
One of my colleagues reported an issue with accessing a database as below. Initially, I thought it was something related to disk unavailability or resource related and asked them to verify those parameters. Later they reached to me saying all are intact with disk and memory.
Database ‘dbname’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
While checking in SSMS, it is also noted that the db was showing as “(Not synchronizing / Recovery Pending)” in Object Explorer GUI.
So, the first thing we need to make sure to get this db out of availability group. To do, we need to remove this database from the availability Group with the below code.
ALTER AVAILABILITY GROUP [AG-Name] REMOVE DATABASE dbname;
Once, we removed the database, we observed the status of the database as “Restoring”. So the next thing was recovering the databases from status “recovering” to “online”. There is another blog post on “How to recover restoring database in SQL Server“. As first step, I tried to get the database by simply restoring the db with recovery option and found the below exception.
Msg 4303, Level 16, State 1, Line 5
The roll forward start point is now at log sequence number (LSN) 67784000000743200001. Additional roll forward past LSN 67785000000710400060 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
The above error message is a clear indication that there is some severe issue with I/O that needs a restore from backups (full + logs) for this database. Then we tried to restore the last transaction log file which again ended up with another message as below.
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 67784000000743200001, which is too recent to apply to the database. An earlier log backup that includes LSN 67785000000710400060 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Since we were not sure of the exact log file, we decided to restore from a most latest valid full backup followed by log backups. Here, is a way to identify backup information of a database.Finally, we were able to get the database online.
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Exception Message
With today’s post, we will deep dive into an error message that one of my friend encountered recently as below.
The data type ntext cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.
Sample Code
Let us first replicate the issue with the below sample code snippet.
Drop Table if exists T1,T2
create Table T1(id int,Col1 ntext)
Create Table T2(id int,Col1 ntext)
Insert into T1 Values(1,'Big value')
Insert into T2 Values(1,'Big value')
Select * From (
Select * From T1
union
Select * From T2)A
The execution of the above code results in the error message and the message is very clear that those specified operators are not allowed with ntext datatype.
Solution1. Avoid NTEXT datatype
NTEXT datatype is a deprecated datatype in SQL Server. Try to avoid in future development and replace the existing with nvarchar(max).
2. Replace UNION with UNION ALL (if possible)
UNION ALL is a clear winner of this situation over UNION operator. So, if you can change the code to replace union with union all, that would be the easiest way. Then why is it so? – Simple, UNION operator does a sort operation internally which is forbidden with datatype ntext whereas UNION ALL does not require sort operation.
Select * From (
Select * From T1
union ALL
Select * From T2)A
3. Construct column list without ntext datatype columns
Drop Table if exists T1,T2
create Table T1(id int,Col1 ntext)
Create Table T2(id int,Col1 ntext)
Insert into T1 Values(1,'Big value')
Insert into T2 Values(1,'Big value')
--Construction of column list without ntext datatype columns
Declare @Col_list varchar(max)
Set @Col_list = (
SELECT STUFF((SELECT ',' + name FROM sys.columns EE
WHERE EE.object_id =E.object_id and system_type_id not in (99)
ORDER BY column_id
FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM sys.columns E where object_id = object_id('T1')
and system_type_id not in (99) group by object_id)
Declare @sql nvarchar(max) = ''
Set @sql = 'Select * From (
Select ' + @Col_list + ' From T1
union
Select ' + @Col_list + ' From T2)A'
exec sp_executesql @sql
Sometime, we may not be able to avoid the ntext column depending on the requirement. In such situation, you may use an explicit conversion of those ntext datatype to nvarchar(max) as below.
--Construction of column list with ntext datatype columns but with explicit conversion of datatype
Declare @Col_list varchar(max)
Set @Col_list = (
SELECT STUFF((SELECT ',' + case when system_type_id= 99 then 'Cast(' + name + ' as nvarchar(max)) as ' + name Else name End FROM sys.columns EE
WHERE EE.object_id =E.object_id
ORDER BY column_id
FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM sys.columns E where object_id = object_id('T1')
group by object_id)
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
Advertisements
This site discusses and shares thoughts and experiences in SQL Server.