Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 20 Pluralsight courses and have written over 3600 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14 years of hands on experience he holds a Masters of Science degree and a number of database certifications.
This was one of an interesting error which I received while preparing for a demo. I wanted to demonstrate a disaster recovery scenario by doing backup and restore. In this blog we would learn how to fix Msg 4353 – Conflicting file relocations have been specified for the file. Only a single WITH MOVE clause should be specified for any logical file name.
To reproduce this error, we need to create a database which has two files which the same “logical” name and different physical name. Creating that is also a tricky and for me, it was created due to my copy/paste mistake. If you or your developers are lazy like me, you can run into this error.
Before running below, make sure C: has a folder called Temp.
CREATE DATABASE [SQLAuthority]
(NAME = 'SQLAuthority', FILENAME = 'C:\Temp\SQLAuthority.mdf')
(NAME = 'SQLAuthority_log', FILENAME = 'C:\Temp\SQLAuthority_log.ldf')
Do you notice any problem in above? Yeah, two physical files having a same logical name.
I took a backup of this database as below.
BACKUP DATABASE SQLAuthority TO DISK = 'C:\Temp\SQLAuthority.bak'
Here is the output
Processed 328 pages for database ‘SQLAuthority’, file ‘SQLAuthority’ on file 1. Processed 8 pages for database ‘SQLAuthority’, file ‘SQLAuthority_D1’ on file 1. Processed 8 pages for database ‘SQLAuthority’, file ‘SQLAuthority_D1’ on file 1. Processed 3 pages for database ‘SQLAuthority’, file ‘SQLAuthority_log’ on file 1. Processed 0 pages for database ‘SQLAuthority’, file ‘SQLAuthority_L1’ on file 1. Processed 0 pages for database ‘SQLAuthority’, file ‘SQLAuthority_L1’ on file 1. BACKUP DATABASE successfully processed 347 pages in 0.050 seconds (54.140 MB/sec).
You can clearly see that backup is happening for all the files, including duplicates. Now, I wanted to restore the database on a different server. Since there was no C:\Temp on the destination server, I decided to relocate the files to a different folder, so I used UI to restore it. Here is the error which I got.
Conflicting File Story
When I looked at T-SQL, here is the one which is failing
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority.bak'
WITH FILE = 1
,MOVE N'SQLAuthority' TO N'F:\DATA\SQLAuthority.mdf'
,MOVE N'SQLAuthority_D1' TO N'F:\DATA\D1.ndf'
,MOVE N'SQLAuthority_D1' TO N'F:\DATA\D2.ndf'
,MOVE N'SQLAuthority_log' TO N'F:\LOG\SQLAuthority_log.ldf'
,MOVE N'SQLAuthority_L1' TO N'F:\LOG\L1.ndf'
,MOVE N'SQLAuthority_L1' TO N'F:\LOG\L2.ndf'
,STATS = 5
As we can see, we have the same logical name twice in the restore statement.
The error message is saying a pretty obvious thing but why did it allow me to do that in the first place? ALTER command which I ran, in the beginning, should have shown the error message to me about conflicting files. However, there is no error for the conflicting files.
Anyways, the workaround would be to create the same path as the source on this server. Basically, we need to avoid MOVE command and let it restore files at the same location where they wanted to go.
Another workaround would be to rename the logical file name using below command.
ALTER DATABASE [SQLAuthority] MODIFY FILE (NAME=N'SQLAuthority_D1', NEWNAME=N'SQLAuthority_D2')
ALTER DATABASE [SQLAuthority] MODIFY FILE (NAME=N'SQLAuthority_L1', NEWNAME=N'SQLAuthority_L2')
Interestingly, the above command changes name only for the one file (out of two in this case). Once renamed then another backup is needed to be restored.
My most of the Comprehensive Database Performance Health Check consulting engagement I do are online and I enjoy working on them when I am at home office and I have multiple monitors. However, I often travel to the customer’s location when my client wants me to mix SQL Server Performance Tuning Practical Workshop with Health Check. This is when I doing a presentation on my laptop and I need every single real estate on my small laptop screen. Let see a neat trick, I use to Maximizing Query Execution Plans On Screen.
By Default SQL Server shows the query and the execution plan in the same windows by giving 50-50% real estate to each of them. However, this is not good enough when you are presenting on a small screen or explaining the concepts to a larger audience. Here is the screenshot of how it looks with the default settings with SQL Server query and execution plans.
However, if you go to Tools >> Options >> Query Results >> SQL Server >> Results to Grid >>
Now over here enable or check the following settings:
Display results in the separate tab >> Switch to results tab after the query executed.
Well, that’s it. It will show maximize the screen for you.
If you want more screen estate you can enable the following shortcut Shift + Alt + Enter to maximize the execution plan by hiding the toolbars.
At midnight I got a call from a customer who earlier availed my service Comprehensive Database Performance Health Check. The issue which they were facing were sudden high CPU for their SQL Server. They sent me an SOS message for help and I responded. The entire issue was about Page and Row level compression. Let us read more about it today.
About 2 months ago, the customer had hired me for a SQL Server Performance Tuning Health Check, during that time we had put lots of performance tuning tweaks. The entire system ran fine for over 2 months and suddenly during one of the night, they once again faced the issue with SQL Server performance, when they reached out to me.
The very first question I asked them was what has changed since everything was working fine and now suddenly things are not working fine. After a while, they presented a list of the activities which they had done on their server since morning. While reviewing the list, one of the activity got my attention and that was they had enabled Page Level on one of the tables which was under heavy use and was the biggest table.
I immediately turned off Page Level Compression and their CPU came down to the earlier levels. After careful observation, we figured out that they did not have enough CPU to enable Page Level Compression. They reached out to their hosting company and added more CPU to their machine. Later on, we turned on the page level for them again and everything went fine.
Let us see scripts to Enable PAGE and ROW Level operation as well as Disable operation.
Page Level Compression
ALTER TABLE [NameofYourTable] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);
Row Level Compression
ALTER TABLE [NameofYourTable] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);
ALTER TABLE [NameofYourTable] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = NONE);
Let me know your feedback and opinion in the comments section. I am always eager to read your experience.
In the last 13 years of blogging, I have been asked this question again and again and so many times that I think if you wake me up in the middle of the night I can easily reply about the difference between Login Vs User – Security Concepts.
SQL Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login is created at the SQL Server instance level and User is created at the SQL Server database level. We can have multiple users from a different database connected to a single login to a server.
Here is a simple image explaining the relationship of Login Vs User in SQL Server instances and database.
Login vs User
Here is the video which describes the same with an easy example:
Difference Between Login and User - SQL in Sixty Seconds #070 - YouTube
Here is the sample script to create a login and map a user to it.
CREATE LOGIN [MarkSmithL] WITH PASSWORD = 'password';
CREATE USER [MarkSmithU] FOR LOGIN [MarkSmithL];
Let me know if you have any further questions about Security Concepts. I will be happy to answer in the comment. Here are a few additional blog posts on the same subject:
I recently had a very simple and interesting error received while I was working with Docker and Persistence Storage for my upcoming SQL Server Performance Tuning Practical Workshop. The error was related to script upgrade mode and the fix was extremely simple. Let us see the entire story today.
Earlier on this blog, I wrote a blog post about SQL SERVER – Docker Volume and Persistent Storage. During the blog post, I explained how with the help of the docker we can instantly upgrade our SQL Server instantly. It was a fantastic feature as if you think when we have to update SQL Server with the latest update, we have to re-install SQL Server and it takes a lot of time. However, with the help of Volume, we can easily upgrade SQL Server in just within a minutes.
Recently, I attempted to upgrade my SQL Server CU to the latest version of SQL Server and instantly I got the following error:
Login failed for user ‘sa’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)
Here is the screenshot of the error.
Well, the solution if this issue is very simple. I just waited for about 1 minute and attempted to log in again and I was able to login immediately. Whenever we are using Docker and Volume, our data remains on the persistent storage but our core engine is updated. Once SQL Server comes up with the new update, it runs few of the upgrade scripts for your database. While the scripts are running the database even though looks up it does not allow you to connect with it.
In most of the cases, the script upgrade takes 1 minute or 2 minutes of time. If you wait for that much and try to log in, your error will go away. Well, if you are here searching for the solution to this problem. I am very confident by the time you finished reading this blog post the error is gone. Just go ahead and try to login again and it should work.
Answer: This is the first question I always ask during my Comprehensive Database Performance Health Check. The system restart date is very important to know but even more so, I always want to know when was SQL Server services were last restarted. When SQL Server services are restarted, many of the Dynamic Management Views (DMV) are automatically emptied out and they lose pretty important history related to SQL Server’s performance.
I always like to work with the system which has been running for a while as it has so much good information hidden in DMVs. However, there have been scenarios when I have to work on the system which is not stable and frequently restarted and those have been always a challenge.
Here is the script which lists when was the last SQL Server services restarted:
You can also know this information by checking when the TempDB was created. Every time when we restart SQL Server services it automatically recreates the TempDB. You can run the following script to know the time of TempDB creation.
WHERE name = 'tempdb'
There is one more method also exsists where we read error log and figure out the start time of SQL Server.
EXEC sp_readerrorlog 0,1,'SQL Server is Starting'
However, my personal observation says that there is always some seconds difference between the information which we get from dm_os_sys_info and sys.databases. Though the difference in the time is negligible, I like to depend more on the DMV sys.dm_os_sys_info.
I have been using Docker for a while to run the latest CTP of SQL Server 2019 and it has been so far amazing ride to use the product. Every time when new CTP is released, I learn something new with Docker. This time I learn that I can just run SQL Server Image without running pull command. Let us discuss it in this blog post.
Before you continue this blog post, I suggest you read two of my previous blog post before continuing this blog post so you can have a proper context of this blog post.
As you can read in the above blog post before we run any image we have to download the image with the help of the Pull command.
However, while I was downloading the SQL Server 2019 CTP 2.5, I decided to run the image directly without downloading the image first.
Here is the command to check the currently available container image on SQL Server.
Now we will directly run the image CTP 2.5 without downloading it. What I learned is that every time when we run any image it first checks if the image is available locally or not. If the image is not available locally, it will automatically download the image from the repository.
Once the image is downloaded, we can run the same command earlier to check the available container image and now it will show us the latest installed container as well.
If we run the container again in the future and if the image is already downloaded, next time it runs the container image instantly.
I am learning new things every day with docker and I am enjoying it.
My primary task is to do consultation for SQL Server Performance Tuning. I help people to tune their SQL Server. If you have any Performance Tuning problem, I would have seen it in the past and most probably I know the solution as well. While doing the Comprehensive Database Performance Health Check, I often show the demonstration of various concept on my personal system. I always use Microsoft provided sample database for my demonstrations. People often ask where can they get the database and how they can install it. Here is the quick tutorial for the same.
There are two primary sample databases available. Let us see where we can download them and how we can install them.
You can download the database bak file from the GitHub link here. You should download file AdventureWorks2017.bak
Run following T-SQL code to restore AdventureWorks database into your server.
RESTORE DATABASE [AdventureWorks2017]
DISK = N'C:\WideWorldImporters-Full\AdventureWorks2017.bak'
WITH FILE = 1,
NOUNLOAD, STATS = 5
Here is the quick video to install sample database AdventureWorks.
You can download the database bak file from the GitHub link here. You should download file WideWorldImporters-Full.bak
Run following T-SQL code to restore WideWorldImporters database into your server.
RESTORE DATABASE [WideWorldImporters]
DISK = N'C:\WideWorldImporters-Full\WideWorldImporters-Full.bak'
Here is the quick video to install sample database WideWorldImporters.
Install SQL Server 2016 Sample Database WideWorldImporters - SQL in Sixty Seconds #081 - YouTube
Well, that’s it. I have everything here which can help you get going with SQL Server Sample databases.
I get to work with various different versions and environment when I am busy with my clients with Comprehensive Database Performance Health Check. While I work with different customers often I forget what data type is supported by which version of SQL Server. Here is a small quick trick, I use all the time when I need to know what are the different data types supported by the version I am working with.
When you run the above script it gives us a very informative output like what kind of datatype it supports, precision, nullability, searchability, scale as well as a prefix to the datatype.
For example, look at the column Searchable. you will see different values there in that column. Here is what it means:
Specifies the search capability of the column type:
1 = Cannot be searched.
2 = Searchable with LIKE.
3 = Searchable with WHERE.
4 = Searchable with WHERE or LIKE.
Well, there are many interesting columns as well. Here is another example column Case Sensitivity. You can see that XML column is case sensitive column.
I really think this small neat script can be very handy when you are busy doing SQL Server Performance Tuning Engagement. If you know such learning nugget, please share with me and I will publish it with due credit to you.
While deploying Always On Availability Group with a client, they found that when they use automatic seeding, the database was not shown on secondary. After digging more, we found that it was there earlier in “Restoring” state when seeding was in progress but then it automatically disappeared from the list of databases on secondary. Here is the option in the wizard.
Here are a few earlier blogs which I wrote about seeding failures.
The important messages about the database, which we tried to seed, are as follows.
Error: 1412, Severity: 16, State: 211. The remote copy of database “DB” has not been rolled forward to a point in time that is encompassed in the local copy of the database log. Starting up database ‘DB’. The database ‘DB’ is marked RESTORING and is in a state that does not allow recovery to be run. Automatic seeding of availability database ‘DB’ in availability group ‘AG’ failed with an unrecoverable error. Correct the problem, then issue an ALTER AVAILABILITY GROUP command to set SEEDING_MODE = AUTOMATIC on the replica to restart seeding.
The last bullet has an interesting message and tells who to restart the seeding. The very first message (Error: 1412, Severity: 16, State: 211.) tells us the cause of the problem.
You need to monitor seeding using DMVs. There are two dynamic management views (DMVs) for monitoring seeding: sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats. One of them shows information about seedings happening currently and “stats” show the historical data about seeding. In this case, seeding failed with SQL Error.
After running the script from my blog (on ALL replicas), we found that a backup job which was taking log backup on “another” replica every 15 minutes. We disabled the job and started seeding again. This time it worked without any issue.
Have you encountered some error during Always On Availability Group? Please share via comments and help others.