age old problem with TempDB in 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

age old problem with TempDB in 2008

Author

Topic

jwoodin
Starting Member

2 Posts

Posted - 2014-11-21 : 09:04:42

yesterday our TempDB grew pretty quickly and I am guessing that the new programmer wrote a query that went amok (since he came to me for suggestions late in the day). anyway - the size (in mgmt studio - properties for tempdb - many ways to get the information) I see that the Size is 15246.63 MB and the avail is 15215.38 MB - kinda makes sense after seeing that query... now the big question - how do I reclaim some of that 'free space' on the physical drive? I have tried stopping the instance since that use to work with things like this a few sql versions ago - and I have tried the ShinkFile with the truncate option - still same file size --- I am missing something easy and have an opportunity over the weekend to bounce things, but what am I missing?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-21 : 10:07:03

I would just set the file sizes for tempdb to some sensible values.
It is up to you to work out what those values will be.

eg


ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', SIZE = 1GB);
ALTER DATABASE tempdb MODIFY FILE (NAME = N'templog', SIZE = 100MB);

or if you do not want to bounce the instance:


USE tempdb;
GO
DBCC SHRINKFILE (tempdev, 1024);
DBCC SHRINKFILE (templog, 100);

jwoodin
Starting Member

2 Posts

Posted - 2014-11-21 : 13:19:17

thanks - did not think about the alter command and had already tried the truncate - will give that a go over the weekend - thanks Ifor!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources