Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


why i can't shrink tempdb data file


why i can't shrink tempdb data file

Author
Message
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6966 Visits: 8838
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7735 Visits: 8713
Microsoft has responded to that feedback thread with a request for more information. Were you the one who posted it?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6966 Visits: 8838
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
erik 92154
erik 92154
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
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
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
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
ignacio.aranda
ignacio.aranda
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 139
I had a tempdb mdf file which was 60 Gb, and I tried shrinkfile, and shrinkdatabase and got nothing. I noticed that I had several databases in 'readonly' mode. I changed them, executed shrinkfile file and reduced tempdb mdf file to 500 Mb, and then I turned them again to 'readonly' mode.
It worked, but I don't know why these readonly dbs didn't allow me to reduce tempdb, do some of you know why???
Thanks and br.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
ignacio.aranda (12/3/2010)
I had a tempdb mdf file which was 60 Gb, and I tried shrinkfile, and shrinkdatabase and got nothing. I noticed that I had several databases in 'readonly' mode. I changed them, executed shrinkfile file and reduced tempdb mdf file to 500 Mb, and then I turned them again to 'readonly' mode.
It worked, but I don't know why these readonly dbs didn't allow me to reduce tempdb, do some of you know why???
Thanks and br.
i dont think there would be any relation between readonly DB and tempdb shrinkage

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7735 Visits: 8713
The only thing I can think of is that the files had open transactions in tempdb. Just because the files are read only doesn't mean they don't use tempdb to process SELECT statements.

But the read only setting itself shouldn't have had anything to do with tempdb. I think either it was a coincidence of timing or that you somehow killed a process doing what you did.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
ignacio.aranda
ignacio.aranda
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 139
Nope, I tried to shrinkfiles and database some times before and I didn't get it reduced. And of course I checked that there were no transactions opened, and there weren't.
Maybe it was a coincidence...
wu.wuqiang
wu.wuqiang
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 525
In my case, which is a SQL2008 R2, I successfully shrank the tempdb by using Method 1 in MS KB
http://support.microsoft.com/kb/307487
A little bit tricky part is I didn't logon by using SA. I do have sysadmin privilege therefore when system asked me to restart by using command line, I hit Ctrl-C to quit and start the SQL services in Management Console.

BTW, I did above on a dev box.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search