October 29, 2008 at 11:26 pm
Hello -
Can someone help me on the below issue :
There is SQL2005 instance on a Development box and TEMPDB is placed on T drive - 30GB. Its grown to the tune of 30GB, consuming all the space on T drive and I have service request for space issue on T drive. There is no activity running on tempdb now but I am unable to shrink tempdb using dbcc shrinkdb at file level. I restarted the instance twice to resolve the issue but again tempdb grows to this volume after any heavy activity on tempdb. Almost no free space is available on T but users are able to run their development activity witout any issue. I noticed that internally lot of free is space is available in tempdb but not able to release it. When I execute sp_spaceused on tempdb, below output is there :
database_name database_size unallocated space
-------------- ---------------- ------------------
tempdb 30645.25 MB 30255.32 MB
reserved data index_size unused
------------- ----------- ------------- -------------
1336 KB 576 KB 640 KB 120 KB
which shows lot of unallocated space inside tempdb. Anyone having any idea how to release unallocated space to OS.
Thnx in advance.
Sunil
A.M.
MIS Deptt.
JCT Mills Ltd.
PHAGWARA
October 29, 2008 at 11:51 pm
probably one of your transactions is a bulk transaction and takes a lot space.thats why u see a reoccuring problem. Do u have any other drive. If yes, disable autogrowth, make max size = 30 GB and create one more file on another drive.
Another option is try to backup of the tempdb before u try truncate it or shrink it. generally for this type of problem for other databases we take a tlog backup but since we cannot backup the tlog of tempdb u can try taking the full backup. restarting the SQL services should always be avoided.
if u have any further problems u can send a mail to meekee26 at yahoo dot com or call nine three two four one six six two four one
October 30, 2008 at 1:40 am
If tempDB keeps growing to that size, it would indicate that it needs to be that size. Rather than keep on shrinking it (it will grow again), is there a larger drive that you can move it to? If not, can you get more drive space?
Shrinking TempDB will slow the server down during the shrink and when the file needs to grow again, it will slow down transactions occurring at that time.
The other thing you may want to investigate is why the app is using so much TempDB space. It may be poorly-written queries, it may be unnecessary temp table creation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 1:44 am
Makarand Mohandas (10/29/2008)
Another option is try to backup of the tempdb before u try truncate it or shrink it.
You can't backup tempDB at all
Backup database tempdb to disk = 'D:\temp.bak'
Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2008 at 1:50 am
oops silly mistake.....
i never had tried to take a backup anyway.
so basically as gail has also agreed, dont restart SQL Server/ reinitialize the Tempdb get more space instead.
If your tempdb wants to get to that size let it infact give it more space.
October 30, 2008 at 2:55 am
Makarand Mohandas (10/30/2008)
If your tempdb wants to get to that size let it infact give it more space.
...or revise you application, there is a reason why TempDB grows that way and the reason is hidding in the transactions the application is issuing.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 30, 2008 at 7:00 am
I'll throw my hat in too.
The main thing I would do would be to determine the transactions that are growing the tempdb to that size. You can use DMV's to determine what queries are running and you can set an agent alert to tell you when tempDB has grown past X MB so you can "set a trap and wait". Another idea is to dump the dmv output to a table every minute so you can see what's going on. There are other tools like Quest's Spotlight or Performance Analysis that can help with this too if you have them.
If it's not one or two particular transactions, but rather that's the size tempdb really needs to be definitely follow the advice of the other posters.
Craig Outcalt
September 24, 2011 at 12:29 pm
can i add one more secondary file (ndf) for temdb on diff run on the fly with out service restart ?
September 24, 2011 at 5:00 pm
this thread is 3 years old!! Theres nothing like raising the dead is there 😀
Please start a new thread in future
You may add the file without restarting
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply