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.
| Author |
Topic |
|
pitmanfan
Starting Member
25 Posts |
 Posted - 2007-08-13 : 04:13:16
|
| the problem i have is that the tempdb mdf file has been slowly growing over the last couple of months and has filled the disk. i've managed to claim a little bit of space by running dbcc shrinkdatabase but as its a production server would rather not stop and start the service. is there a way of finding out what it taking up all the space and deleting it to reclaim the space? |
|
|
YogeshDesai
Posting Yak Master
136 Posts |
Posted - 2007-08-13 : 08:34:29
|
| Hi,You should have mention the ConfigurationWhich version of SQL server you are using.The tempdb database is created on the master database device. The default size of tempdb is 2 MB. If you use large temporary tables, or run many queries with GROUP BY or ORDER BY, or frequently use large cursors, then the size of the tempdb database should be increased. To increase the size of the tempdb database, you can expand the master device and then expand the tempdb,Tempdb database contains only temporary objects, so if you want to create a permanent object, do not create it in the tempdb database..Please check is there permanent objects in tempdb.Yogesh V. Desai. | SQLDBA| |
 |
|
|
pitmanfan
Starting Member
25 Posts |
Posted - 2007-08-13 : 10:22:52
|
| there arent any permanent objects in tempdb. the server i'm using is 2005 but a solution using 2000 aswell would be very helpful. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-13 : 16:06:22
|
| Sql2k5 uses tempdb in different way from sql2k, take look at this article:http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx |
|
|
|
YogeshDesai
Posting Yak Master
136 Posts |
Posted - 2007-08-14 : 11:48:36
|
| hi rmiao,This is really useful to understand tempdb.Good article.Thanks for sharing buddyYogesh V. Desai. | SQLDBA| |
|
|
|
pjstreiff
Starting Member
12 Posts |
Posted - 2007-10-02 : 12:39:09
|
| Here is a KB article that explains methods for shrinking tempdb file:[url]http://support.microsoft.com/kb/307487[/url]If Production enviromnent really has to be online 24x7x365, and can't tolerate a 5 minute outage to restart sql services occasionally, you're going to have a hard time performing some required maintenance or recovery, in my opinion.Thanks,PhilPhil Streiff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 16:27:46
|
You can NOT shrink TEMPDB. Period.That is the policy you must adopt.To vary from this policy you can employ my services at $1,000 per minute.This price, whilst harsh, is to get you to first think carefully about managing the space that TEMPDB needs on your installation.Here is the salient paragraph from Microsoft's KB article on the subject in question:"You must run DBCC SHRINKFILE command while no other activity occurs in the tempdb database. To make sure that other processes cannot use tempdb while DBCC SHRINKFILE executes, you must restart SQL Server in the single user mode"If you haven't spotted it yet: rebooting should fix the problem; if it makes you feel better to reboot to get the server into Single User Mode so you can shrink TEMDB, and then rebooting to get it back to MultiUser mode, that's fine. That's only 30 seconds of my time, please send $500 in used notes to the normal address ...Did I miss anything?  Kristen |
|
|
|
|
|
|
|
|
shrinking tempdb