tempdb shrinking
-
Hi,
Need some quick help here.
I am unable to shrink tempdb files. There is lot of available free space.
DBCC loginfo output
We have an alert and incident opened for this and we have to close it.
Other thing is that systems/storage team is not going to give us more space.
There are no open transactions, I don't know why I am not able to shrink these db's. I know they are unevenly distributed, I mean sizing and autogrowth settings but as of now , looking for a way to getting some space released to OS.
Also is there a way to fix this without SQL Server restart.
Regards,
Sam
- This topic was modified 4 years, 9 months ago by vsamantha35.
-
This post helped me in a similar situation. There might be a couple of other things you need to do but that was a good place to start.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi Neil,
It worked. after clearing the cache, was able to shrink the files.
Thanks a lot for the help. Was reading similar blog post.
https://sqlsunday.com/2013/08/11/shrinking-tempdb-without-restarting-sql-server/
-
A follow up question, how does clearing of CACHE is related shrinking the database?
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
If I remember rightly it's something to do with execution plans that use temp tables but don't take my word for that.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Getting back to your original post above, stop trying to return stuff to the operating system by shrinking TempDB. It's not returning anything because, presumably and based on the fact that you said your infrastructure team said they wouldn't give you more space, it's permanently allocated for just one thing... TempDB, period.
And that notion of yours about there being a lot of free space? How do you think the individual files got as big as they are? The answer is that there was NO free space at some point in time that will happen again, which makes shrinking the files a totally wasted effort because they're just going to grow again unless you find and fix what is causing them to grow.
The only other time to do a shrink of files on TempDB is to make them all the same size and to give you some headroom for growth alerts to help you find out what is causing TempDB to grow. You're not actually doing the operating system any favors by supposedly returning some of the disk space on a drive that has been designated for use as TempDB only. None, nada, naught.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply