1

I understand that on restart of SQL server (or) shrinking the Database will release the de-allocated memory, unfortunately that is not a suitable workaround for my client.

If the temp db size is 10mb is grown into 500mb after execution of a procedure temp db has free size of 490mb ..IS there any way that we can free the allocated size and reagain it .Make the tempdb back to 10mb without restart or shrinking manually ??

Jasen
  • 11,837
  • 2
  • 30
  • 48
Saravana
  • 13
  • 6
  • Why? If there is a regular operation that requires the database (any database, tempdb included) to be a certain size, why go through the exercise of shrinking it only to have it grow again? Even with instant file initialization, it still takes time to grow the database. And if you're worried about the memory that it's taking, don't. SQL Server is smart enough to dump the memory pages that haven't been used in a while when that's required (i.e. under memory pressure). – Ben Thul Jan 07 '15 at 11:15
  • The temp is growing to a very large size but the used space is only 10% of allocated size..Due to the growth say 90gb the storage medium is getting full.Is there an way so that we can resize the tempdb automaticcaly to the needed 10% of the space and free the remaining? – Saravana Jan 08 '15 at 11:49
  • But that's my point. You obviously have ongoing needs for tempdb to be that large. By shrinking it, you ten the rush of something else claiming that Soave and tempdb not being able to grow as large as it needs to be. What's going to fail when that happens? – Ben Thul Jan 08 '15 at 14:24

2 Answers2

1

Restart or shrinking are going to be the most impactful options here, but you (at your own risk) use DBCC to clear out some of the caches and buffers (ie: DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS)

You are probably already aware, but restart doesn't actually shrink the size. With the instance restarts, a new tempdb is created entirely to the default size auto-growing by the configured rates. This is one of the areas many people first consider when optimizing tempdb for an instance. It's usually best to set your initial tempdb size to a size that should not need to be expanded so you don't have to auto-grow it at all.

The shrinking is resizing the files, but that is usually not desired, especially if you run the risk of doing it while open transactions exist on the db that could cause corruption during a shrink.

Regardless of the approach, definitely be very careful doing something like this.

Jason W
  • 13,026
  • 3
  • 31
  • 62
1

Shrinking is a bad habit. It may lead you to fragmentation.

TempDb size is re-initialized when SQL Server services restarts. So, if you have initial size as 20 GB then after restarting your SQL will have 20 GB tempdb file. Cleaning buffers and cache will not shrink your TempDb files. Cleaning buffers and cache are not recommended to do on production environment on business hours until you have a very good reason to do so.

Best practice is to have a TempDb size optimal for your SQL operation; you can have multiple TempDb files and give SQL an advantage of working in parallel. I have been working on large DW solutions, larger than 4 TB and we restarts our services every weekend (non-business hours).

In worst case also, do not shrink your file until you are in single use mode and you don't love SQL Server any more.

Anuj Tripathi
  • 2,251
  • 14
  • 18