SQL SERVER – TDE Effects on TempDB’s Slow Performance

I am a big supporter of the fact that testing and playing with new features should not be done on production environments. It can prove to be harmful, even after you tried to clean up the system. Sometimes we are not sure what the impact of our acts can lead to. In a recent consulting engagement, I figured this out the hard way. I was brought in for slow performance suddenly and we were not sure what was happening in the system. I love tough challenges and this was no different.

Read More

SQL SERVER – Error Msg 5042, Level 16 – The File ‘tempdev02’ Cannot be Removed Because it is Not Empty

As a SQL Server DBA you would know that sometimes it is possible to make mistakes with the database. Few of the time it can be corrected. Out of them, some are easy and some are difficult. I was running out of space on my VM and I started hunting for a bigger file on my machine. It was not difficult to find and recall that I added some files to the TempDB database while doing some testing. Here is the command to get details about the database file, which gave me Error Msg 5042.

Read More

SQL SERVER – How to Start SQL Server Service Without tempdb?

Before I start explaining the situation, I got into while experimenting with the SQL Server instance on my machine, let me air a word of caution – Please DONOT try this on your production environments. Unless you want to grab unwanted attention from your seniors, this is not something I would want you to ever experiment. In a recent configuration change on my local instance, I was facing an unique issue as I was standing in front of an audience for a SQL Server based session. Rarely does it happen that I get into Murphy’s law of demo not working in front of an audience because I carry multiple backups. Interestingly, this time when I tried to connect to the SQL Server using SSMS, it just refused to connect. I was completely taken aback and what happened after that is a different story about how to start SQL Server Service.

Read More

SQL Server 2016 – Enhancements with TempDB

SQL Server 2016 is loaded with tons of new enhancements that I can’t get enough of it. I have been playing around with the bits since multiple CTP’s and it is getting difficult to catch up on all the releases coming so fast and the number of enhancements from performance and scalability point of view have been too many to track. Let us see in this blog post Enhancements with TempDB in SQL Server 2016.

Read More

Interview Question of the Week #048 – How to Move TempDB to Another Drive?

In a recent interview when the interviewer asked interview that TempDB can be moved to a different drive,  the candidate was extremely surprised. For the benefits of the interviewee who did not know the answer, I have written this blog post. Question: How to move TempDB to another drive? Answer:…
Read More

SQL SERVER – New features in SQL Server 2016 Setup Wizard

In this world of change, the SQL Server product is also changing. One thing which is very clear is that Microsoft is listening to feedbacks from customers and enhancing the setup experience also. Some of the classic feedback implementation can be seen in the setup of the next version of SQL Server 2016. In this blog post, I will try to bring out some of the enhancements that are being incorporated in the Setup UI. Some are critical and worth making a note. Let us learn about SQL Server 2016 Setup Wizard.

Read More
Menu