New answers tagged

1

If you are going to bounce the server then nothing to do. Just define the files and the initial size and they will be built with the initial size when you restart the service. It does not shrink as nothing is retained in a log file.


2

That looks like a data page (page type=1) from a table variable or temp table (negative object id of -1948083318) in the leaf of a clustered index (indexid=1). From the allocation information and the fact that this does not show up in your second query presumably the underlying object has been dropped. You may be able to get additional information about it, ...


2

Restart SQL Server and see if they suddenly match. There is a documented behaviour where if tempdb grows you can get different results depending on how you query what size it is. For example sys.master_files shows tempdb size on start; sys.database_files shows actual size (as do some of the Fileproperty() function results). Nacho Alonso Portillo wrote on ...


0

You have some good answers I had to look it up and turns out you can shrink tempdb Yes the the space is used by other queries When you restart the server it will start at the initial size. I don't know if under the covers it shrinks of just allocates fresh. Growing the tempdb is somewhat expensive so unless you really need the space back then ...


1

How does the tempdb work? If an initial size is 10 GB and then query grows tempdb to let’s say 100 GB and then terminates does the space become available for future tempdb queries/temp tables etc..? Or does it release the space to the operating system? SQL Server does not release space back to the operating system unless you tell it to release or ...


1

After growth the space will be available to future queries. The size will reset after a restart but file growths are expensive so if you have queries that are causing your TempDB to grow then you should really look at manually growing it out to match your requirements. Data file growths can be made considerably less expensive, though still not free, if ...


5

Tempdb is recreated whenever the instance is restarted. At startup tempdb is created at the size specified in the system catalog. This means if you define tempdb to be 10GB, and it grows over time to 100GB, then you restart the server, tempdb will now be 10GB again. The question about shrinking is one loaded with bad connotations. For a discussion around ...


5

Like all databases, free (unallocated) space in tempdb is reused for new objects. No action is needed for this space to be reused. The physical size of tempdb files will reflect the high-water mark of growth since the instance was last started. The size will revert back to the initial file size after an instance restart, freeing space on the disk volume ...



Top 50 recent answers are included