TempDB files don’t exist/are not reachable anymore, what to do?

TempDB is one of four(well, actually five with ‘mssqlsystemresource’ but that’s not the point of this blog post) system databases of SQL Server that holds temporary user and system objects for various operations, such as temp tables or sorting operations.

It is a crucial item on the startup checklist for SQL Database engine service. At every restart, tempdb is recreated and starts empty to serve its role. Any problem that you may face during this creation operation will cause the database service fail to start.

The Scenario

Let’s imagine a scenario where there are 2 tempdb data files defined, one of them is on E drive and the other is on F drive. What happens if we lose F drive? It won’t crash the instance but you will start seeing alerts in SQL Agent logs such as

“The operating system returned the error ‘2(The system cannot find the file specified.)’ while attempting ‘GetVolumePathName’ on ‘F:\temp2.ndf’ at ‘storagedmv.cpp'(443).”

Clearly, SQL Server is trying to access the defined tempdb data file but it doesn’t exist anymore, so it fails.

The first idea that comes to mind is to remove those file(s) and we should be okay. But you can’t just remove them, because SQL Server can’t remove a file if it is not empty.

USE [tempdb]
GO
ALTER DATABASE [tempdb]  REMOVE FILE [temp2]
GO

Msg 5042, Level 16, State 1, Line 3
The file ‘temp2’ cannot be removed because it is not empty.

Then how about emptying the file first and then remove it? No, it will not work also because we can’t access the file anymore.

USE [tempdb]
GO
DBCC SHRINKFILE (N'temp2', EMPTYFILE)
GO

Msg 823, Level 24, State 2, Line 3
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000001f3402000 in file ‘F:\temp2.ndf’. Additional messages in the SQL Server error log and operating system error log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The fix

We have to tell SQL Server that we lost these files and during next startup, it should create them on another available disk. How do we do that? By giving a new path for tempdb database files using ALTER DATABASE command.

USE [tempdb]
GO
ALTER DATABASE [tempdb] MODIFY FILE 
(name = temp2, filename = 'E:\temp2.ndf')--New available path
GO

The file “temp2” has been modified in the system catalog. The new path will be used the next time the database is started.

As can be seen in the result output, SQL updated its system catalog and will try to create this data file on given network path.

We can also verify this change by querying master_files dmv

SELECT * FROM sys.master_files WHERE database_id=2

As long as there is enough space on that drive, SQL should start without any problem and create tempdb files as configured.

If there is not enough space, then it will create tempdb files based on model database configuration. This will also be logged in error log file and sql agent log as

Message
MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file 'E:\temp2.ndf'.

SQL Server without tempdb?

One day you may want / need to start SQL Server without tempdb, maybe because tempdb drive is corrupt or not reachable and SQL doesn’t start successfully. Then you can start database engine service in a minimal configuration mode with the -f command-line parameter, either from SQL Configuration Manager tool by setting Service Properties or use the command-line

SQLServr.exe -f

Alternatively, you can use Trace Flag 3608 which only starts master database

SQLServr.exe -T3608

After bringing the service up, you can connect to it using SQLCMD command line tool and change the paths of tempdb files to a suitable folder. Now we can stop the database service, restart it and tempdb will be automatically created on new path without any issue.

SQL Server XML Conversion Error: “Conversion of one or more characters from XML to target collation impossible”

A quick and short post this time regarding a sql server error being thrown when you try to convert xml characters. XML column has an unicode character that can not be converted into ASCII, thus our engine is not happy about it.

Let’s say you have a “Table1” which stores a “XMLColumn” and you’d like to query on that column with LIKE operator. Hence, you convert it to varchar data type first.

SELECT XMLColumn FROM Table1
WHERE Convert(varchar(max), XMLColumn) IS LIKE %ARollingStone%

Then, you’ll get below error:

Msg 6355, Level 16, State 1, Line 2
Conversion of one or more characters from XML to target collation impossible

To fix this, you need to simply change varchar to nvarchar, that’s it.

SELECT XMLColumn FROM Table1
WHERE Convert(nvarchar(max), XMLColumn) IS LIKE %ARollingStone%

Query Store stopped working after Windows Update on Database Host

Firstly, This is not going to be a long, detailed blog post. I would like to share an issue with Query Store that I experienced today after Windows Updates on SQL Server hosts and a quick fix for that.

Average Wait time is too high on SQL Server

I was doing sanity check after windows updates and noticed severe wait times on a server, caused by a specific database. This very database had a problematic select query which confuses optimizer to choose a wrong execution plan. Query Store helped me to address the issue in the past, I forced the right plan and move on.

I immediately went after the same query and realized it is the one that causes high wait times and lock issues. I checked Query Store and everything looked fine. My forced plan was still there and according to query store statistics, average execution time was 10 miliseconds for problematic query. However, I can see that query takes 10-15 seconds by checking system dmvs. Obviously query was not running with right execution plan. So how can that be and what’s wrong here?

Query Store stopped collecting data

It took some time for me to realize Query Store statistics stop around 7 AM in the morning, coinciding Windows Update time. For some reason, Query Store stopped working and collecting data. That’s why, it was not reflecting what is going on currently and I was blind.

I checked Disk usage and Query Store Space, both were fine. Then I tried to unforce and force the same plan, it didn’t help either. I couldn’t restart the instance or the database so I needed something else to provide quick fix.

FIX – Purge Query Store Data!

I correctly assumed that purging query data would trigger a mechanism internally to restart Query Store and decided to go for it. Voilà! Right execution plan is picked up right away by Query Optimizer, all locks are released and database performance went back to normal. I am not sure why query store stopped working after server reboots for Windows updates. To be able to identify the relation and what was the underlying reason, one needs a proper deep-dive investigation.

You can purge query data by right clicking on database, going into “Properties” and then jump on “Query Store” tab. There you will see a button at the bottom as also shown in below image.

Error: 18456, Severity: 14, State: 5 Login failed for a member of AD group

This is officially my first blog post. I was planning to write a blog for the last 2 years but couldn’t start until now, so here we go.

Last Friday, we ran into a weird login failed error message for a login who is a member of an AD group with sysadmin rights on a new installed SQL Server. I tried conventional methods to find root cause and fix it but I have never experienced this case before. So, I decided to write a blog post about it as my first post!

One of my colleague reported that he can not login into a newly installed Always On Secondary SQL server(let’s call it SQL2) from another database host(let’s call it Host1). I checked error log immediately and saw this errors:

This is a typical login failure which a dba sees a lot, however the problem is he is a member of an AD group(let’s call it [AD\Group1]) and [AD\Group1] exists on SQL2 with sysadmin privileges! Furthermore, I am also a member of [AD\Group1] and I can connect to SQL2 as other members. Also, [AD\Group1] is exists on SQL1, primary replica of this Availability Group, and he connected that server without any problem. Things were getting interesting.

First, I created his domain account directly, he was able to connect both SQL1 and SQL2. This way, we eliminated the possibility that his account is not able to login. But why he couldn’t connect through an privileged AD group? There was something going on with AD group and his membership. I used xp_logininfo procedure to retrieve information about [AD\Group1] members and I saw him as a member, so why the hell SQL does not allow him to login and says there is no such login?

I patched both servers with latest SP and CU, didn’t help.

We removed his account from AD group and added him again, didn’t help.

We removed [AD\Group1] from SQL2 and created again with sysadmin role, didn’t help.

Even though I am embarrassed to tell this, I restarted SQL service, of course didn’t help.

I further searched this issue on internet, also didn’t help.

Then, I installed SSMS on SQL2 host and asked him to try to connect directly from database host, this time it worked. Now, I am even more confused.

So, We have an AD group with sysadmin role which contains 2 members in it. One of them can connect to both SQL1 and SQL2 from anywhere(Host1, local workstation, SQL2 host and so on) without problem, other member can connect to SQL1 without a problem. He can also connect to SQL2 as a member of [AD\Group1] from his workstation and SQL2 host. However, he can’t connect to SQL2 from Host1.

I realized that this needs more careful and detailed examination. I went back to basics and read about AD groups, kerberos authentication and SQL Server authentication-authorization. Then, my colleague told me that he removed himself from [AD\Group1] one week ago and re-added 1 day before this incident occurred. That moment I realized that his kerberos ticket was different on AD than the one cached in his user profile on Host1 server. I asked him to log off from Host1 and log on again, only then he could connect to SQL2 from Host1.

To sum up, If you encounter a similar login failure make sure that domain user is logged off and logged in again!