0

In our production DB, we only have 1 tempdb file and it had ballooned to 180GB.

I ran:

Alter Database tempdb
modify file (name='tempdev', size=30gb);

to resize the primary file, and then ran additional:

ALTER DATABASE tempdb
ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData\tempdb2.ndf', Size=30GB);

to add additional tempdb files. I ran into a drive space error, as the primary did not shrink to 30gb and the operation was halted mid-way.

I am now left with one additional tempdb file that is 30GB while the primary is 180GB and I am unable to run get rid of it.

I have tried running

DBCC SHRINKFILE('tempdev2', EMPTYFILE)

but I get an error:

Could not locate file 'tempdev2' for database 'tempdb' in sys.database_files.
The file either does not exist, or was dropped.

I feel this error is because the operation was not able to complete successfully. (Do I have corruption?)

And when I try to run

ALTER DATABASE tempdb
REMOVE FILE tempdev2;

I get the following error:

The file "M:\SQLData\tempdb2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 5042, Level 16, State 1, Line 35
The file 'tempdev2' cannot be removed because it is not empty.

The sys.database_files looks like this enter image description here

Results from tempdb.sys.all_objects enter image description here

Sys.database_files

enter image description here

Edit: I was able to resolve the 'file not found' filename corruption that i was receiving when trying to run the EMPTYFILE Shrink. I used the File ID instead of the logical name.

DBCC SHRINKFILE(3,EMPTYFILE); -- File ID instead of logical name.

Another method I found that worked was renaming the file.

ALTER DATABASE tempdb MODIFY FILE (NAME =tempdev2, NEWNAME =tempdev3);
  • As it's a production system that doesn't have any set maintenance windows, I would really prefer to not have to restart it. – Asher Feb 7 '18 at 6:23
1

So your current situation is a full drive where you have two TempDB's that you want to modify the old one's size and the newly created one is having difficulties being removed?

There are limitations for use of the DBCC SHRINKDATABASE command on the tempdb database. The target size for data and log files cannot be smaller than the size that is specified when the database was created or smaller than the last size that was explicitly set by using a file-size-changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the command. Another limitation of BCC SHRINKDATABASE is the calculation of the target_percentage parameter and its dependency on the current space that is used.

Try running:

dbcc shrinkdatabase (tempddev, '70') 

This should set your file to ~54GB which should be a safe size considering I am assuming your size or model was 30GB. Adjust the % accordingly.

Microsoft - How to shrink tempdb

Now for the second file, give this a try:

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

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

If the issue persists, you'll have to restart SQL Server to remove the file as TempDB is in use.

Microsoft Forum - File in use

SQL Authority - Shrink and remove Tempdb file

One last note, you typed this:

ALTER DATABASE tempdb ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData**tempdb3**.ndf', Size=30GB);

Your screenshot shows tempdb2, make sure your accurate with all the names and double check them. Just in case there is a mistype and that is causing your headaches.

There are some cases where running

DBCC FREEPROCCACHE

can help alleviate the pain and allow you to shrink the file.

Ozar - When TempDB won't shrink.

If you are still having issues, see what is out in your tempdb:

select * from tempdb.sys.all_objects
where is_ms_shipped = 0;

This will let you see your size of the tempdb files.

USE [tempdb]
SELECT
   [name]
   ,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2))                                 AS [Size]
   ,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2))            AS [Used]
   ,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],'SpaceUsed'))/128.,2))      AS [Unused]
FROM [sys].[database_files]

From your post we see that you have files used in TempDB and there is space free. SQLSunday by Daniel Hutmacher has a good link on shrinking your DB with additional methods that were similar to Ozar, I would recommend to give this a try as well with caution.

There is a post by Mike Good - SQL Central on shrinking your TempDB by adjusting the snapshot level of TempDB with a rollback period to kill any connections that may have lingered on your db.

  • Would it be possible to put the newly added tempdb datafile into read only mode and then delete the contents of it before trying to run the emptyfile and remove command? – Asher Feb 11 '18 at 12:31
  • Adding to my previous comment. I was able to get a substantial reduction in size in both tempdb files using Daniel Hutmacher's method - but this did not allow me to remove the file. – Asher Feb 11 '18 at 12:55
  • Did it gave the same error saying it was too full to remove even with the reduction in data / size? If you run the script to alter the size of the file and add the alter remove tempdb file immediately after it and execute it at once, does it remove the file or continue with the same error? – Shaulinator Feb 12 '18 at 19:21
  • I was able to schedule a restart and that ended up releasing the lock on the file. Didn't require any further commands to get it removed, it was no longer listed in the sys.database_files - so I then manually deleted the file. Thanks for your help. – Asher Feb 12 '18 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.