3

I have a spring project, when I start the server a named file 'tempdb' is created in the sql server directory, the size of this file is too big (reaches 8G) I like to know why this file is created? is there a way to reduce its size? Thanks in advance

Akino
  • 303
  • 1
  • 4
  • 16
  • Microsoft SQL Server Documentation - [tempdb Database](https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database). If you don't want tempdb to be the size it is at start up, change it's Initial File Size on it data file(s) (ensuring they all have the same intial size). – Thom A Jan 04 '18 at 11:48
  • Thanks @Larnu for your answer. But I can't understand why this file is generated when I start tomcat server (Run my project). So I don't know which configuration I should change because I communicate with DB server only whith ip adress I don't manipulate it! – Akino Jan 04 '18 at 12:01
  • tempdb is basically what it says it is, a **temp**orary **d**ata**b**ase. It's there so that the SQl Server data engine can write out data to disk to work with. If the Server doesn't have enough memory for the query it's doing, these will spill over into tempdb, where it will write out it's work tables, etc, to disk. The reason it's 8GB in size is because that's what you've set it to. You can check the initial size of tempdb on SSMS by `Object Explorer->Expand Your Instance->Expand Datases->Expand System Databases->Right Click tempdb->Properties->Files`. If you lower the size, restart instance – Thom A Jan 04 '18 at 12:14

2 Answers2

9

run this 

-- use the proper DB
USE tempdb;
GO
-- sync
CHECKPOINT; 
GO
-- drop buffers
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
GO
--shrink db (file_name, size_in_MB)
DBCC SHRINKFILE (TEMPDEV, 1024);
GO

Either, you can always restart whole Database (like service restart), it will also drop the tempdb.

mati kepa
  • 2,543
  • 19
  • 24
  • I restarted the MSSQL Service from `services.msc` – however it didn't decrease the temp file size. Which services are you referring to when you talk about service restarting in your answer? – Lars Blumberg Sep 27 '19 at 13:38
  • 1
    I am talking about MSSQLSERVER service itself, but to be sure you can also restart SQLWriter and MSSQLFDLauncher – mati kepa Oct 03 '19 at 13:54
0

The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores. It is also used to store Worktables that hold intermediate results that are created during query processing and sorting.

You can use "ALTER DATABASE tempdb" command to limit/shrink the size of this.

The size 8Gb is suspicious though as tempdb size is seen mostly in MBs. You might want to check for any ugly queries when you're starting up your server.

Vishal Raja
  • 303
  • 1
  • 8
  • 1
    I woulnd't say that the size is suspicious. My SQL Server has 4 data files for tempdb, each 3GB in size. The Initial size could be anything, and depends on what you use the server for. – Thom A Jan 04 '18 at 12:12