November 23, 2009 at 11:23 am
right now, unallocated_extent_page_count in sys.dm_db_file_space_usage is plenty.(TempDB has space), but we are not able to shrink it. shrinkfile sometimes back with the extent info, sometimes takes a long time, but the bottom line is it does not shrink the file. (for example, TempDB is 100MB, is currently using 2MB, and we want to shrink the DB to 50MB, but not able to.
The tech reason, I suppose is, even though there is enough space and only little of TempDB is being used, there is some current operation that blocks the shrinking process? Can someone explain this technically. Is it a 'tail' data that will not allow any shrinking unless that piece of data is truncated/down away with by the system process itself etc. Can we somehow push the TempDb data all into one corner, so we can shrink it etc.
thank you for your input.
November 23, 2009 at 11:21 pm
Heh... you're doing one of the worst things possible. I don't know how big your TempDB was before you started shrinking it but set it to 10% larger than that and then leave it alone. Just to make you feel better, on small systems I'll start TempDB at 1GB. Depending on the size and type of the larger systems, I may pre-allocate TempDB as 8 different 10 GB files for a total of 80GB.
If you have a smaller system, set TempDB as big as you can afford to set it.... 50 MB isn't going to do it for you... it'll just grow eventually.
If you don't have much hard disk space, go buy a couple. They're cheap now a days.
But, whatever you do, stop shrinking TempDB once it has grown.
--Jeff Moden
November 24, 2009 at 5:41 am
I am with you in principle. I am asking what is technically going on inside, that may block a shrink, even when there is enough unused space.
November 25, 2009 at 7:52 am
I agree with Jeff, this is a very bad idea. Generally speaking, with any version of SQL Server starting with 2005 it is very difficult to shrink a tempdb because it is in constant use, not just by user operations, but also by SQL Server itself. It is SQL Server's general working area, containing all intermediate query results, etc. When you use the shrink command, SQL Server tries to use tempdb as a working area, stepping on itself. You could possibly add a separate physical file to the current tempdb on the same drive, turn off SQL Server completely, then swap the file names between the two physical files and restart SQL Server. Then reconfigure tempdb to remove the second file (what used to be the first one).
Because the later versions of SQL Server rely so much more on tempdb, many DBAs with a lot of experience exclusively on earlier versions tend to under allocate space for tempdb. In 2008 especially. 2008 allows snapshot shadowing in an effort to avoid locking read access to queries competing with very large update processes on the same tables. This can use HUGE amounts of tempdb. Microsoft advises the following:
allocate as many physical files as there are CPUs.
tempdb space in total should be, at minimum, as big as the largest anticipated intermediate result set you can anticipate plus 50%.
tempdb should never be assigned to RAID-5 storage, since there is constant writing to it. Use RAID-10 for tempdb if you have to use RAID, because, even though it takes twiice as much physical disk space, it is much faster.
My largest SQL Server server instance is using 24 processors and about 40 TB. The largest single DB file is about 0.5 TB. The tempdb database is allocated as the only db files on a RAID-10 logical SAN drive of 1.5 TB. Because of the complex research queries run on this instance, the tempdb files take up nearly all of the space. The drive is dedicated, so there is never any need to shrink or adjust it.
November 25, 2009 at 10:03 am
What SQL Server Version you are using?Is it SQL Server 2005. I faced similar in issue in 2005 in one of our production server. I tried to shrink the file after seeing the empty space in tempdb. Command runs successfully, but it will not shrink anything.For that night we restarted the Server and Tempdb came back to normal,but after few days same issue,tempdb grows abnormally.
The resolution for this issue we implemented is created tempdb data files as many as equal to number of processors in the server. Then we never came across this problem again till now.
November 25, 2009 at 12:31 pm
repent_kog_is_near (11/24/2009)
I am with you in principle. I am asking what is technically going on inside, that may block a shrink, even when there is enough unused space.
Ah... got you. "Irish" pretty much tagged it in his post above. It's pretty much the same as any other database in that if the space being used isn't right at the "beginning" of the underlying file, it will only shrink to whereever the data ends in the file.
You might actually want to take a look at what's in the TempDB... there could be some procs, UDF's views, or tables in there that folks have put in during "experiments". If memory serves correctly, user objects that you create in the MODEL database will also show up in the TempDB database immediately after it's rebuilt on boot up. Those, of course, would also keep you from shrinking if the don't appear right at the beginning of the file.
--Jeff Moden
November 25, 2009 at 2:54 pm
VRR (11/25/2009)
What SQL Server Version you are using?Is it SQL Server 2005. I faced similar in issue in 2005 in one of our production server. I tried to shrink the file after seeing the empty space in tempdb. Command runs successfully, but it will not shrink anything.For that night we restarted the Server and Tempdb came back to normal,but after few days same issue,tempdb grows abnormally.The resolution for this issue we implemented is created tempdb data files as many as equal to number of processors in the server. Then we never came across this problem again till now.
We use both 2005 and 2008. It doesn't matter, since they both use tempdb for their working space. I don't see how you can say tempdb grows abnormally, since it will only grow to the size the instance needs. The fact that it has unused space doesn't matter at all. The unused space will be reclaimed when you restart the server, but it will also be used for any processes you, or anyone else, run. I have never seen any tempdb growth that could be called abnormal, it just is what it is. Even if you could shrink it, if the space is occasionally needed it will just grow back. You cannot reassign that space to any other databases even if you shrink the file, because if you make tempdb too small, your instance will perform like it is running in thick mud, even if it doesn't crash.
November 25, 2009 at 3:27 pm
Ditto to everything already said here, don't shrink it, don't shrink it when it's in use, etc. Best practices and all that.
But...in the interest of knowing why it won't shrink for you as expected...I read somewhere (technet perhaps?) that 'stuff' hangs out in tempdb, just in case it may be needed. These aren't technically 'open' transactions, or even related to anything currently running. But SQL won't allow them to go away, just so you can shrink the darn tempdb.
In the interest of 'if all else fails', and you can't stop & restart SQL (which will reset tempdb to it's previously defined size), you can issue this command
DBCC FREESYSTEMCACHE ('All')
Which will cause all heck to break loose and quite possibly bring your system to its knees...but...you can now run a
DBCC SHRINKFILE ('tempdev',some # in MB) or DBCC SHRINKFILE ('tempdev',TRUNCATEONLY)
that will shrink your tempdb.
With caveats.
Lori
November 25, 2009 at 9:20 pm
Irish Flyer (11/25/2009)
I have never seen any tempdb growth that could be called abnormal, it just is what it is
Heh... man, I have. Developer ran some code that had an accidental cross join between a table with 7 million rows and another table with 700,000 rows... I got the alert from some dead paniced bosses that had customs saying they couldn't even login because the system was so slow. By the time I could get to it, the damned thing had grown to 75GB and was still growing. That's what I call "abnormal growth".
There was also an "alert" put out that if you gave the growth a MB value (I think it was something that 2k5 sp2 fixed), that the system would mistake it as a percentage. Imagine the surprise on folks when you wanted to to set the growth to 500 MB and it instead grew by 500%. 😉 Again, abnormal growth.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply