I am not a proponent of shrinking databases, but sometimes you have to because your momma said to. Sometimes that database is tempdb.
It used to be that we were warned against shrinking tempdb because it could cause corruption, so your only recourse was to restart the SQL Server service. Paul Randal let us know that this is no longer a problem.
LET’S SAY YOU HAVE TO SHRINK TEMPDB
Like your life depended on it. Or perhaps you needed the alerts to stop.
If a user ran an atrocious adhoc query that caused your tempdb to grow so much that it caused your disk space alert to fire and:
- you needed that alert to stop
- the storage team is not going to give you more space
- the user promised to never do that again
So you try to shrink tempdb, but it just won’t shrink.
Try clearing the plan cache:
DBCC FREEPROCCACHE
And then try shrinking tempdb again.
I came across this solution recently when I had to shrink tempdb. I tried shrinking each of the 8 data files plus CHECKPOINTs, repeatedly. It would not budge. I almost threw in the towel and emailed out that the space issue would be fixed during our next patching window, but then I found David Levy’s reply. DBCC FREEPROCCACHE worked like a charm.
Brent says:
71 Comments. Leave new
I have found that more often then not DBCC FREESYSTEMCACHE (‘ALL’) (also mentioned in David’s comment you reference) is more effective than FREEPROCCACHE (although FREEPROCCACHE does often work) – I have never dug into why one is more effective than the other (for me anyway).
This has worked for me 99% of the time.
http://sqlsunday.com/2013/08/11/shrinking-tempdb-without-restarting-sql-server/
Sadly, I’ve used this link more than I care to admit. But, when you can’t take down an instance, it works. I’d say it’s worked 90% of the time from my personal experience.
Thanks: This got the last 6 extra tempdb files (out of the unnecessary 31) an third party vendor dumped all over my instance! I can now sleep at night.
A handy article … for some of the not well-versed DBAs it might be worth mentioning the downsides of running DBCC FREEPROCCACHE before some people go and run this command on their production or client-facing pre-production instances.
I can add a disclaimer, but the point I was trying to convey was that there is an option to shrink tempdb without restarting SQL when shrinking isn’t reducing the file size. I’m not suggesting to run this command for no good reason.
What are the downsides?
@David Warner … What are the downsides?
It’s a cool trick, sure, but all magic has a price.
In this case, you’ll blow away each and every plan in your plan cache and have to recompile them all. Thereby incurring a CPU hit. Might kill your performance for a bit, might not. Depends on the number of plans and how complex they are.
Can we have a disclaimer about running this in production added to the article? At the very least, can you add an explanation of what exactly this command will do?
Indeed! This is a situation where you are trying to fix a problem (disk space alerts) and not have to restart SQL. Clearing the procedure cache is a much better option than the downtime incurred from restarting SQL. But I hear you on the possible negative side effects of this. Blowing out good plans and possibly getting bad plans is a risk, plus all the overhead of the compiles. Been there, done that.
i mean… restarting sql also clears all the cache so…
Not an option in PaaS
Marco – can you be more specific about what you think isn’t an option in PaaS, and which PaaS vendor/solution you’re using? You might be surprised.
We’re using Azure SQL Managed Instance so don’t have the ability to restart SQL ourselves
Marco – in Azure Managed Instances, you shouldn’t be shrinking TempDB, so this article is irrelevant.
We’re in a situation where tempdb has grown to over 200gb after some one off processes. We’re on business critical 8 vCore which means we’re only allowed a maximum of 1TB unless we spend 10k extra per month to upscale to 24 vCore for 2TB
Marco – gotcha. It’s fairly normal to expect that TempDB will be around 20-25% of the size of the user databases. People are gonna dump stuff into TempDB when you’re not looking.
If you have around 800GB of data, a 200GB of TempDB wouldn’t be unusual at all. I’d leave it there. (If you’re trying to cram every single last byte into the 1TB, then you’re probably better off stopping to review tables you’re not using, indexes, etc.)
Just a quick follow up – looks like our TempDB shrunk from 200GB to 10GB by itself! Probably as part of Microsoft’s behind-the-scenes routine maintenance?
I thought that was well known along with FREESYSTEMCACHE (‘ALL’), FREESESSIONCACHE and DROPCLEANBUFFERS.
Nonetheless, cool tip!
I’ve been around for quite some time and had never come across it. Was surprised it worked!
I ran into this problem yesterday, using the SSMS task to shrink files never accomplished anything, then I re-read the documentation on Shrink File and realized I needed to try this:
DBCC SHRINKFILE (N’tempdev’, NOTRUNCATE) — Move allocated pages from end of file to top of file
DBCC SHRINKFILE (N’tempdev’ , 0, TRUNCATEONLY) — Drop unallocated pages from end of file
I did this for all 8 of the tempdev files and slowly, over time, it finally recovered about 80% of the space. I was truly surprised by this as nothing else had worked. I had to run a job every 15 minutes through the day but it eventually recovered the space.
Excellent! Thanks that did it for me 🙂
Nope..it did not worked for me..
That worked. Thanks Mike
Thanks for the article. I think microsoft should make it more clear how and when use this command or should include more options in the GUID. For me, it’s still not totally clear when use shrink commands and what’s the advantage of it 🙂
The best practice is to not shrink your databases. Sometimes you have to for storage reasons. Sometimes you do it because of a large one-time delete, and you know you aren’t going to need that size for a long time.
Never shrink on a schedule. Only do it manually. Understand why the files grew and the ramifications of shrinking them.
More often than not a look through the plan cache will tell you who thinks your prod server is a dev server.
Would this really be necessary ? FREEPROCCACHE will reset all execution plans. This command clears the cache for the entire SQL instance, all databases. To clear for just one database, use the following:
DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master. dbo.sysdatabases WHERE name = ‘TempDB’ )
–Flush stored procedure/plan cache for the specific database
DBCC FLUSHPROCINDB (@intDBID )
I am not sure if it was necessary or not. I was in a bind, saw David’s post and tried it. Next time I’ll try your code.
I like that!
Nothing like a surgical strike.
This is much simpler, unless I’m missing something in terms of reliability:
DBCC FLUSHPROCINDB (DB_ID(‘TempDB’))
I had a related issue today. Although tempdb was fine, the log was auto-growing like crazy. Once I got the insanity to stop, I couldn’t get that log to shrink (log_reuse_wait_desc was ‘ACTIVE_TRANSACTION’). Executing CHECKPOINT freed it up so I could shrink it back to its normal size.
Yeah CHECKPOINT didn’t work in my case. That was one of the first things I tried.
I expect that it will only work on the tempdb log (and only in some circumstances, not the tempdb database files.
We have a couple of instances where we can’t restart the instance without involving the 3rd party vendor, less than Ideal, we are working on that.
For now we have added a separate drive for tempdb and pregrown the tempdb much like https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/ several months ago.
Before that:
When I just couldn’t wait, services are starting to timeout and running ‘
DBCC FREEPROCCACHE’ has not helped and alerts are going off everywhere (i.e. a Hail Mary script) I have run the script below (modified to suit the number and naming convention of the tempdb files.
Performance would be demonstrably quicker in a minute or so.
How long the whole script takes to run depends on a lot of factors.
Disclaimer: we are not talking financial transactions or other end of the world type stuff where the transactions cannot be re submitted. Test in your lab on your apps before you need it if you can.
It’s dirty, but it works.
— Report existing file sizes
use tempdb
GO
SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO
DBCC FREEPROCCACHE — clean cache
DBCC DROPCLEANBUFFERS — clean buffers
DBCC FREESYSTEMCACHE (‘ALL’) — clean system cache
DBCC FREESESSIONCACHE — clean session cache
DBCC SHRINKDATABASE(tempdb, 10); — shrink tempdb
dbcc shrinkfile (‘tempdev’) — shrink default db file
dbcc shrinkfile (‘tempdev2’) — shrink db file tempdev2
dbcc shrinkfile (‘tempdev3’) — shrink db file tempdev3
dbcc shrinkfile (‘tempdev4’) — shrink db file tempdev4
dbcc shrinkfile (‘templog’) — shrink log file
GO
— report the new file sizes
SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO
I wonder how it resolved a performance problem. I mean, I get how it can resolve a performance problem for a user database when you can’t figure out which stored proc to recompile. But I wonder how it is resolving a performance problem when you run that script for tempdb. I’m wondering if it’s because of the size of the procedure cache.
Primarily in waits caused by disk thrashing was dropping like a stone.
In the specific instance we had issues with the previous Admin had left tempdb on c.
Since I have been able to get all the ducks in a row for a full restart and reconfigured TEMPDB on its own drive, preinflated to max size etc, issue has not reared its ugly head.
The sort of query (recursive that caused temp db to grow by30gb and keep growing ) that killed it still runs form time to time but doesn’t hold up the rest.
So restarting SQL to shrink tempdb would do this:
FREEPROCCACHE
DROPCLEANBUFFERS
FREESYSTEMCACHE
By executing those commands instead of restarting SQL you are saving yourself an outage. You have to pay the price somehow. But I would rather that than an restart scenario.
You may find that none of those commands actually work as many of you have. I expect this is because some queries have started in the mean time and hit tempdb again… doh! Try running the shrink and the DBCC between a BEGIN and END statement, that may help.
I have found the command that also works is DBCC SHRINKFILE (N’tempdev’ , EMPTYFILE)
Yes you have to expand it again. This time you are going to fix size the tempdb files and set it to what you believe is correct based on your knowledge of the system, then deal with the users complaining their (crazy) reporting transactions are dying.
Saving us from an outage was my number one goal. Number two was to stop the disk space alert. And if there were performance problems that arose because I cleared out the procedure cache, I was prepared to deal with that. Always monitor after making production changes!
In my case, I had to free the procedure cache several times. We had 8 tempdb data files, so I probably ran it 1-2 times per file before we were back to the size before the user ran the large query.
It is a handy trick when you find yourself with a disk space issue, but really be sure to set your tempdb max file sizes so you don’t.
Jon – that would seem to produce a different problem, right? Your TempDB runs out of internal space, and then what?
(Just making sure you don’t think this permanently solves the problem either.)
…and then as an operations DBA you don’t get paged at 3 AM to sort out a 0% free diskspace issue. 😉
You can then shame the developers during normal business hours.
I’m not a fan of setting max file sizes. I’d rather queries don’t get rolled back and deal with the disk space issue by having proper alerting in place. Now of course a user query could still fill the entire disk and fill it so quickly that any alerts in place are too late, but that should not be a frequent problem if the disk is sized appropriately for the workload. The disk in question here was over 500GB in size and dedicated to tempdb. This was a massive user query that ran and was not typical. The user did get the data since the query was able to complete. That would not have been the case if we had set max file size at 80% of the total disk size.
Old thread but couldn’t help but responding. Identifying the root cause and how to avoid it is step one. The advice here is great but the real problem is that the words User and Query are in the same sentence. So the DBA is scrambling to resolve an issue with application design (or lack thereof) or SQL best practices. Adhoc user queries in my experience are generally a bad idea, queries that could take a server offline should be prevented by putting into an application or a reporting module like SSRS with sane limits wrapped around them and if something that large is needed the DBA gets involved. I would rather the query fail with programmatic or SQL file size limits, make them submit a ticket and ask why it failed and try to explain why they are running a query like that in the first place.
You wrote:
Errr, okay, back here on Planet Earth, we don’t usually get the ability to say, “Users aren’t allowed to run queries.”
But I sure like the sounds of your planet. Which one is it, by the way?
None of the above given workarounds are working in my case. I have a SQL 2005 SP3 environment. Every time I do shrink I get below message.
DBCC SHRINKFILE: Page 1:2325704 could not be moved because it is a work table page.
Have tried above workarounds multiple times but it did not work. 🙁
That means TempDB is actively in use. You may be able to see who’s using it with sp_WhoIsActive.
Thanks this worked for me. sp_spaceused reported 11GB free out of a 12GB tempdb data file. Tempdb on c. Only 2GB free on c. Server guys wont give me any more space until the next scheduled outage (weeks).
This solved the problem.
Thanks a lot. I had the same problem and worked very fine…. 🙂
I like the approach Marcy posted. I myself like to dig into the procedure cache individually for the production env; Usually it’s some rogue developer causing havoc. However, I have seen some procs kill tempdb quickly like this one… just to return analysis info…
SELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END – qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC — logical reads
— ORDER BY qs.total_logical_writes DESC — logical writes
— ORDER BY qs.total_worker_time DESC — CPU time
*Quickly bookmarks this post*
Thanks for the tip, I’ve been in this situation quite a few times, and I’ve sadly had to restart SQL to make tempdb go back to normal 🙁
This worked the way it is supposed to. Thank you for your tip. I have this issue for last 3 days and finally i could get this solution. Thank you again.
USE dbcc opentran and look for any open tran before you try to shrink the tempdb.
Sometimes you have to restart it in single-user method. And, in my case, also undo-the max connections of 1. :
These commands work:
NET START “SQL Server (SQLEXPRESS)” /m”SQLCMD”
sqlcmd -S .\SQLEXPRESS
EXECUTE sys.sp_configure
@configname = ‘show advanced options’,
@configvalue = 1;
RECONFIGURE;
EXECUTE sys.sp_configure
@configname = ‘user connections’,
@configvalue = 0;
RECONFIGURE;
use tempdb
go
dbcc shrinkfile (tempdev, 5)
go
— this command shrinks the primary data file
dbcc shrinkfile (templog, 5)
go
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files
I’d rather not shrink tempdb if I have to take a downtime like that. Do you have any links for when single-user mode is necessary? I’ve never had to do that. I’ve always been able to shrink even if I had to do all sorts of steps.
Somehow I’ve had this issue multiple times but never come across or considered this as a solution. Until today, when I found this article, and it worked. Useful bit of info.
It worked like a charm !
Thanks Brent!
This worked for me!
But like others have mentioned, after you run this, you will have to look for a root cause, as it may just go right back to growing the database again.
YUUUP! http://i0.kym-cdn.com/photos/images/original/000/580/956/f3e.jpeg
I used sp_WhoIsActive to get to the root cause of this. It was really clear once I filtered down to the timeframe in question and sorted by the tempdb allocation column. I then sent an email to the business user who wrote and was running the query.
In my case, sp_whoIsActive did not help, as it was not a query per se; it was the fact that TempDB was not configured to the recommended best practice, which Brent has mentioned before (128mb data files, etc). The files were set to initialize at 20GB each, and as soon as the user tried running their job, it was maxing out TempDB. I ran the DBCC statement, released the space, shrank data files down to where they need to be, bounced the services (with the user’s blessing), and all was good.
[…] to Tara Kizer who’s post gave me the idea of clearing the plan […]
When a temp db file will not shrink, check its initial allocation.
Yea, Temp DB file #1, #3, and #4 where configured at 20 gig. TempDB #2 was configured at 56 Gb as initial extent or file size.
This might make a great test question.
There are no good test questions. (If you’ve ever attended Office Hours when we’ve been asked about certifications, you’ll know I’m pretty against them.) Plus I wouldn’t be a good DBA if my tempdb files were not all the same size and have the same autogrowth.
No good test questions? Are there any good answers ? If so, how do we find them answers ?
I agree about Temp DB files. Ran into this one today on a DB I did not build but was asked to fix. It will take three weeks to fix the file size: – (sigh) life by multiple committee.
One other repair – Temp DB uses a different collation than the database.
Anyone else notice that the size reported by master.sys.master_files can be quite different to the size reported by tempdb.sys.database_files?
Any ideas why that is?
This is across versions 2008R2, 2012, 2014, 2016 and only appears to be tempdb – none of the other databases show this discrepancy.
Excellent. I spent 45 minutes trying to shrink Tempdb but it won’t work. It worked before and that is what led me to wrong direction. Now evrything is good.
Great tips Tara and from the comments above.
I had two tempdb data files that had blown out to 208GB. After trying your steps I managed to shrink to 10GB, hurray… outage averted but still had the other pesky file not playing nice.
Tried it multiple times (definition of lunacy) and then found that increasing the file by a few megabytes and then running the dbcc shrinkfile allowed me to shrink both files to manageable numbers.
Thanks for the tip. Worked like magic.
What I am wondering, though, is why tempdb is tied to the procedure cache? I’m trying to figure out why this works.
Thanks again
When it comes to “why” questions, you would need to ask Microsoft, right? I can’t really tell you why Microsoft does what they do. Hope that’s fair.