January 9, 2014 at 5:23 pm
My Tempdb is growing over 95%...I can't seem to find a way to clear it.
These are some of the things that I did so far but no luck. This is PROD server and cannot reboot at the moment.
I tried to shrink Tempdb and each files, but not getting anywhere.
I also tried to see all the processes and see if any process using Tempdb so, I can kill it, but I can't seem to find any spid using Tempdb.
Last thing I did is, I ran dbcc freeprocache against Tempdb to see if it will clear some Tempdb cache but still no help either.
What else can you do?....please share your experience as I'm lost what else to do other than rebooting the sql service.
Regards,
SQLisAwe5oMe.
January 10, 2014 at 4:15 am
You have processes that are doing things that use tempdb such as sorts or index rebuilds or something else. You need to identify these processes, figure out how long it's going to take them to complete and then either wait them out or kill them. BUT, if you kill them, you'll be dealing with a rollback situation which will possibly expand the use of temporary space, so be very careful. You can look to queries against sys.dm_db_file_space_usage to identify what is using space on the database.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 10, 2014 at 4:23 am
Why are you trying to shrink it? Surely if the database is 95% full (there's almost no free space left), you'd be looking to grow the database (make more free space available) rather than shrink it (release the free space to the OS resulting in a 100% full database)
In the short term, if things are using TempDB and it's full, you need to grow the files. In the long term, consider identifying what's using TempDB heavily and tune the queries so that they do not need so much TempDB space.
FreeProcCache clears the plan cache, resulting in heavier load as the optimiser compiles all queries again, it won't clear 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
January 10, 2014 at 5:46 am
To add to what Gail said, the ONLY 2 reasons you should consider shrinking TempDB would be:
1) A runaway query that has caused TempDB to grow exponentially beyond the usual needed size. But before you can shrink the DB, you need to identify and kill the runaway query. Fix the query. Run a checkpoint. Then shrink the TempDB.
2) You have next to no hard drive space left on the server and nothing else is working. Shrink is a TEMPORARY remedy (to be avoided if possible and used sparingly if not possible) until you add more space to the server.
Unfortunately, if you've gotten into one of the two above situations, then you have a business problem instead of a SQL problem. But if there is no runaway query and TempDB needs 95% of the current space, than you really do have that many processes running that requires that much space.
In which case (as Gail said and it bears repeating), you shouldn't be shrinking but growing instead.
January 10, 2014 at 5:55 am
GilaMonster (1/10/2014)
Why are you trying to shrink it? Surely if the database is 95% full (there's almost no free space left), you'd be looking to grow the database (make more free space available) rather than shrink it (release the free space to the OS resulting in a 100% full database)In the short term, if things are using TempDB and it's full, you need to grow the files. In the long term, consider identifying what's using TempDB heavily and tune the queries so that they do not need so much TempDB space.
FreeProcCache clears the plan cache, resulting in heavier load as the optimiser compiles all queries again, it won't clear TempDB.
Thanks guys for the response.
Actually my drive is grown more than 95% and this drive holds all system db files and Tempdb files are the biggest, I.e. 43gb size of Tempdb data file and 3 gb log file. The remaining are the other system db files, which is pretty small.
Regards,
SQLisAwe5oMe.
January 10, 2014 at 5:59 am
Then you need to:
Figure out what cause TempDB to grow (see Grant's post)
Determine if what caused it to grow is normal or was is a once-off operation, a broken query or similar
If normal, you need more drive space for TempDB
If not normal, then you need to tune queries and ensure that whatever it was doesn't happen again.
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
January 10, 2014 at 8:13 am
RUn this see if a process has a High Allocation and Low deallocation.
Are you running Service Broker ( a badly written service broker)?
select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply