why i can't shrink tempdb data file
-
I have the same issue, the tempdb mdf file will not shrink even though I have attempted all known commands to do so. A reboot did not change the size. My size is now at 140GB, with at least 99% free space...it should start out at 100MB. OK, I shouldn't say it doesn't shrink, because at one point it shrunk from 100GB to 70GB, then went up to 150GB in an hour and then it shrunk to 140GB after I did the shrinkfile and db commands. I might as well fall on my sword.
-
briansalentine (11/19/2009)
I have the same issue, the tempdb mdf file will not shrink even though I have attempted all known commands to do so. A reboot did not change the size. My size is now at 140GB, with at least 99% free space...it should start out at 100MB. OK, I shouldn't say it doesn't shrink, because at one point it shrunk from 100GB to 70GB, then went up to 150GB in an hour and then it shrunk to 140GB after I did the shrinkfile and db commands. I might as well fall on my sword.Brian, if it shrank down to 70GB and then grew back to 140GB, than you should not be shrinking as it would appear you have a lot of activity against the tempdb which is requiring a large amount of disk space.
-
I found that a department was running reports for a very large date range, so, this brought our normally 7GB- 10GB database up quite a bit. We basically want to stop run away processes and large reports from happening as they can get the data in a different way, so I want to limit this to 20GB.
In any case, I guess I'll try another reboot later. And if all else fails, delete the tempdb in off hours.
-
If you have snapshot row-versioning in any of your user DBs, this very fact will prevent you from shrinking tempdb. I had to shrink such a tempdb this week, trick was to take user DB out of that mode temporarily while I shrank tempdb. Here's script I used:
--check which dbs are in snapshot isoation modeselect name, is_read_committed_snapshot_on, snapshot_isolation_state, snapshot_isolation_state_desc, *
from sys.databases
order by 1
go
--take DB out of this mode temprarily in order to shrink it
alter database MY_USER_DB set READ_COMMITTED_SNAPSHOT off with rollback after 30 seconds
go
use tempdb
go
--select 16* 1024 --(convert GB to MB)
dbcc shrinkfile (tempdev, 16384)
go
select (size*8)/1024 as FileSizeMB from sys.database_files--check new size
go
--restore original DB settings
alter database MY_USER_DB set READ_COMMITTED_SNAPSHOT on with rollback after 30 seconds
go
-
Mike, that's really interesting. Want to write that up as an article?
-
Ha ha! I pretty much just posted the full extent of my knowledge on this subject!
We've never designed-in row-versioning anywhere I've worked. But recently we had a 3rd party application here that suffered from bad blocking problems, and at vendor's request we turned on the READ_COMMITTED_SNAPSHOT setting. Seemed to help with the blocking, and didn't impact tempdb too seriously.
Until this weekend. tempdb autogrew to max size, just about filled it's drive. I tried to shrink it, but could not. Tried all the std stuff listed above to see what was preventing me, no good. After about 24hrs the idea above occurred to me, and it worked.
I've since found some queries that will show how much of tempdb is consumed by row-versioning, wish I'd had them handy this weekend. As it is, I'm not sure if the row-versioning or something else was responsible for the massive/rapid tempdb growth we experienced this weekend. http://msdn.microsoft.com/en-us/library/ms176029(SQL.90).aspx
-
briansalentine (11/19/2009)
I found that a department was running reports for a very large date range, so, this brought our normally 7GB- 10GB database up quite a bit. We basically want to stop run away processes and large reports from happening as they can get the data in a different way, so I want to limit this to 20GB.Restricting the size of the temp db will NOT resolve your problem. It will only cause a lot of deadlocks and your server freezing every time the tempdb fills up.
To stop the large reports, find out what stored procedures / views / tables the reports are running against and what security login they are using. Then selectively deny Select permissions.
If the culprits all belong to one group, stick them all in an Active Directory security group (assuming they aren't already in one), then deny select permissions to that group.
EDIT: Or delete (rename) the culprit reports if you can't deny permissions for whatever reason.
-
-
ive really enjoyed this thread. I have not had this issue, but i do want to investigate what processes are using the most space in my tempdb.
Anyone give me a quick query to show what processes are using tempdb, or will a standard sp_who or sys.dm_exec_requests do just fine?
-
as stated in all kb stuff already pointed to, you cannot shink tempdb without stop/start of the sqlinstance. (an even if it would succeed, chances are your ongoing operations may produce errors because of your action)
However .... there is hope ... but you'll need to vote for it at http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=496872
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Microsoft has responded to that feedback thread with a request for more information. Were you the one who posted it?
-
Brandie Tarvin (11/20/2009)
Microsoft has responded to that feedback thread with a request for more information. Were you the one who posted it?No, it wasn't me.
Because this shrinking of tempdb frequently comes back in the fora, I googled - ehm - binged 😉 for a previous reply series and found the connect item.
I was looking for a forum post which stated shrinking tempdb might cause "unstable" issues to your instance, but couldn't find it.
However I did find the KB: http://support.microsoft.com/kb/307487#6 stating:
Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In Use
If tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors similar to the following type and the shrink operation may fail:
Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.
-or-
Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.
Although error 2501 may not be indicative of any corruption in tempdb, it causes the shrink operation to fail. On the other hand, error 8909 could indicate corruption in the tempdb database. Restart SQL Server to re-create tempdb and clean up the consistency errors. However, keep in mind that there could be other reasons for physical data corruption errors like error 8909 and those include input/output subsystem problems.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I had the same problem. There was no way to shrink the tempdb datafile. Eventually I was able to clear the immovable objects from the tempdb and then shrink the tempdb. Below DBCC statements will impact performance (since you're flushing SQL Server's cache), but you won't disconnect your sessions. It's a better option than bouncing the service in my point of view.
DBCC FREESYSTEMCACHE ('ALL')DBCC FREEPROCCACHE
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 1000)
I found this information here: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/2a00c314-f35e-4900-babb-f42dcde1944b
-
Temp DB cannot be shrunk as long as it is in use. Sql server can use it for the internal operations or for row versioning or for storing the user objects like temp tables and variables.
Some times so many transactions will be runing on the TempDB that they are so short in duation that it is very hard to notice them. Even this will prevennt Tempdb from shrinking.
For ex: Running DBCC OPENTRAN may result in no transactions for the first time. But when run for the second time it may show you any active transaction.
In most cases restarting TempDB should fix the prolem. But this should be your last option.
Thank You,
Best Regards,
SQL Buddy
Viewing 15 posts - 31 through 45 (of 66 total)
You must be logged in to reply to this topic. Login to reply