SQLGeeksPro

Remove or Delete Tempdb data file without restarting SQL Server

by Dev on

You may want to delete that extra Tempdb data file which someone created by mistake or to accommodate a query. Whatever may be the reason, today I am going to show you how to do it and what issues you may face.

You can run the below query to remove the data file:

ALTER DATABASE tempdb
REMOVE FILE LogicalFileName;
GO

Or you can use GUI:

SQL Server throws the following error:

The error message says that it cannot remove the file because it is not empty.

If you just add a file and there is no ongoing activity in the file then it would allow to remove the file this way.

For each file you want to remove, you need to run the following command to empty the file and then run the above query to remove the file:
USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO

USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO

But what if it throws the below error:

The error message is about a work table page which can not be removed. Work table is related to cached information, which indicates that you need to clear the cache.

Execute the below queries one by one and after clearing each, try to empty the file again:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

The successful run shows as below:

Please keep in mind that whenever you are clearing cache, it can cause performance issues. I am leaving the decision up to you to handle the risk factor according to the environment.

If your environment allows you to restart SQL Service you can just restart which will empty the tempdb files and you can run the remove command:

ALTER DATABASE tempdb
REMOVE FILE LogincalName;
GO

I hope you have already debunked the Myth around Remote Query Timeout option.

You can go ahead and watch this video:

Please spread the word:

Remote Query Timeout Option – Common SQL Server Misconception

by Dev on

I have seen whenever customer complains about Query Time Out issue, there is a tendency to play around with this settings. This does not work the way you might think it does.

I would like to quote from TechNet:

https://technet.microsoft.com/en-us/library/ms177457(v=sql.105).aspx

Use the remote query timeout option to specify how long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.

It clearly says, this applies to only outgoing connection and does not have any effect on incoming queries. The misunderstood part here is, what is outgoing and what is incoming query for SQL Server. This should get clarified once you read through this article.

Here is my LAB setup for the Demo:

As you can see in the above screen shot, I have set the remote query timeout setting for the default instance to 5 sec(s).

You can execute the below query to set ‘Remote Query Timeout’ settings:

sp_configure 'Remote Query Timeout', 5
GO
RECONFIGURE
GO

Kept the default settings for the Linked Server SQL Instance (SQL_Named). i.e. 600 Sec(s)

Now if I run a remote query to linked server that would be an outgoing query for the default instance and incoming for Linked Server.

In Linked server I am keeping an open transaction so that the lock on the table [AdventureWorks2014].[Production].[Product] does not get released.

Initiate a remote query from the default instance (Outgoing Query).

If you can see that the query got timed out exactly after 5 sec(s). So, it proves that the remote query time out settings applies for remote query (i.e. for outgoing query).

Now lets see how it works for incoming query. Keep and open transaction on the same table. This time executing it in the default instance.

Open a different session for the default instance and run select on the same table. Both the queries are incoming for the default instance.

This time query is still waiting even though it crossed 12 sec(s) without getting timed out. This was an incoming query for the database engine of default instance and hence Remote Query Timeout Settings (which is set to 5 sec(s)) did not apply.

I hope this clarifies how exactly “Remote Query Timeout” option works.

Remove or Delete Tempdb data file without restarting SQL Server

You can go ahead and watch this video:

Remote Query Timeout Option – Common SQL Server Misconception

More over if you like video training check the demo here: