Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

why i can't shrink tempdb data file Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 3:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #822200
Posted Friday, November 20, 2009 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:54 AM
Points: 5,579, Visits: 6,363
Microsoft has responded to that feedback thread with a request for more information. Were you the one who posted it?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #822237
Posted Friday, November 20, 2009 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #822258
Posted Thursday, September 9, 2010 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 18, 2011 12:05 AM
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
Post #983430
Posted Thursday, September 9, 2010 7:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:50 PM
Points: 1,194, Visits: 2,220
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

Post #983534
Posted Friday, December 3, 2010 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:00 AM
Points: 3, Visits: 63
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.
Post #1029863
Posted Monday, December 6, 2010 3:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:01 AM
Points: 2,840, Visits: 3,968
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
Post #1030515
Posted Monday, December 6, 2010 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:54 AM
Points: 5,579, Visits: 6,363
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1030535
Posted Monday, December 6, 2010 1:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:00 AM
Points: 3, Visits: 63
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...
Post #1030827
Posted Tuesday, December 7, 2010 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:56 PM
Points: 20, Visits: 473
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.
Post #1031408
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse