TempDB shrinkfile help! Please

[SteveMe]

I've set the size of the tempdb to 100Gb. Every night I'd like to run a job that shrinks all contents of the data and log file and have it grow back or stay at 100gb. The reason for this is that I don't want the DB to grow out everyday. 100gb should be sufficient. The other day I was told to do the following. Can someone confirm and or give any suggestions that would help ease my thoughts. Thanks

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)
GO
DBCC SHRINKFILE (N'templog' , 0, TRUNCATEONLY)
GO

 

[earthandfire]

Personally, I've found DBCC SHRINKFILE (<filename>, 0, TRUNCATEONLY) unreliable by itself.


I now use:

USE <database>
DBCC SHRINKFILE (1, 0)
DBCC SHRINKFILE (1, 0, TRUNCATEONLY)
DBCC SHRINKFILE (2, 0)
DBCC SHRINKFILE (2, 0, TRUNCATEONLY)

Hope this helps.

 

[SteveMe]

earthandfire why the 1st and 3rd line. Thanks for responding I'm just trying to understand all of this better. My end goal is to have the tempdb at 100gb with almost no data inside. So that the next morning's processes where temporary tables are used in the tempdb that those tables will not grow the tempdb therefore slowing down processes.

DBCC SHRINKFILE (1, 0)
DBCC SHRINKFILE (1, 0, TRUNCATEONLY)
DBCC SHRINKFILE (2, 0)
DBCC SHRINKFILE (2, 0, TRUNCATEONLY)

 

[earthandfire]

From BOL:

target_size

Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

TRUNCATEONLY

Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.

To be honest I've no idea. My requirements were to strip the log file to the bare minimum and I found that neither one of:

DBCC SHRINKFILE (2, 0)
DBCC SHRINKFILE (2, 0, TRUNCATEONLY)


worked every time - but when using both it works.


Hope this helps.

 

[mrdenny]

If the tempdb grows above 100 Gigs daily then your tempdb database needs to be larger than 100 Gigs. All you are doing by shrinking it daily is putting more load on the disks for no reason and giving you self a false sence of security with relation to how much free drive space you have.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SteveMe]

Thanks much appreciated.

Denny thanks for the input. I should of stated that I want the tempdb to stay at 100gb daily. We have a few processes that can take up 10-35 gb of data. On a rare occassion another process can chew up another 5-10 gb. In the past the tempdb was out of control. We would shrink it and then watch it grow like jaba the hut. Usually we took care of the DB when it grew to a crazy size. Recently it grew to 170gb. But that was due to not having a job in place that would reduce the size. I know that currently 100GB is generous. But I'm allowing it to be this size for future processes plus so that I don't have to revisit.