February 13, 2015 at 8:20 am
Hi - I'm getting pretty low on disk space on my C drive due to a large tempdb (7gb) in size & as a result only have 30mb in size available so need to do something this weekend before users are back on Monday morning.
I've heard that restarting the Server should clear the tempdb, is this correct?
Any advice is very much appreciated!
Kind Regards
Dax
February 13, 2015 at 8:35 am
For starters, you should read this article to gain a better understanding of tempdb and what it's for.
https://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
There are many opinions on tempdb, this is only one of them.
Are the disks on the server local, or are they on a SAN? Regardless, having tempdb on the C drive is probably not a best practice.
You only need to stop the SQL services, delete the .mdf and .ldf files, and re-start the services.
tempdb will be re-created.
BUT. This is not a solution. It is a temporary fix. tempdb will grow again. That's normal. Determining what may be abnormal growth, and correcting this, is the challenge.
A 7GB tempdb may not be considered large. How big are the rest of the DB's on the instance? I have mine sized at 120GB on my production box.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 13, 2015 at 8:46 am
Hi - Many thanks for that, I shall have a read through the article. If I were to restart the Server would this reduce the size, or do you have to physically delete the tempdb mdf & ldf? Just a bit dubious about deleting these files!
The System DB's are located on the C drive & the User DB's are located on a D drive. The largest DB is 11gb in size & on the D drive there is 129gb available space.
Is it worthwhile also moving the tempdb to the D drive? If so do you have any instructions for this?
Thank you very much for your help, it is appreciated!
Kind Regards
Dax
February 13, 2015 at 9:08 am
If you restart SQL Server, it should go to the last size tempdb was set to. If that's smaller than it is now, it should be smaller.
February 13, 2015 at 9:33 am
dax.latchford (2/13/2015)
If I were to restart the Server would this reduce the size, or do you have to physically delete the tempdb mdf & ldf?
Most of the time yes it will resize the files. Occasionally, I have seen the files stuck at the last size they grew to.
You can also, usually, shrink the files using dbcc shrinkfile without a Server outage.
Just a bit dubious about deleting these files!
The tempdb files can be deleted without concern while the SQL Service is stopped. They will recreate upon service restart. Again, this requires an outage.
Is it worthwhile also moving the tempdb to the D drive? If so do you have any instructions for this?
Generally it is better to have the tempdb files on a separate volume than the OS volume. However, if all you have is a logical division between the drives (and it is not separate physical drives), then you are not likely to see a gain in performance. It is also generally better to keep tempdb on a separate physical volume from the user databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2015 at 10:09 am
Many thanks for all the comments/advice. I have restarted the Server & the TEMPDB is now down to a sensible size. I shall carry out some analysis to work out what's eating up the space.
Many thanks again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply