February 22, 2011 at 7:06 am
Hi,
i have a prod database of around 340 gb .
it has a tempdb file size of 10 gb.
I ran this command:- ( in a job at night)
USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb', 10, TRUNCATEONLY)
nothing happened
Also, i tried to do the same task via SSMS gui, but it didnt help.
How can i shrink the tempdb
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 22, 2011 at 7:12 am
SKYBVI (2/22/2011)
Hi,i have a prod database of around 340 gb .
it has a tempdb file size of 10 gb.
I ran this command:- ( in a job at night)
USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb', 10, TRUNCATEONLY)
nothing happened
Also, i tried to do the same task via SSMS gui, but it didnt help.
How can i shrink the tempdb
Regards,
Sushant
try
DBCC FREEPROCCACHE
GO
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 500)
DBCC SHRINKFILE (N'tempdev2' , 500)
DBCC SHRINKFILE (N'tempdev3' , 500)
DBCC SHRINKFILE (N'tempdev4' , 500)
DBCC SHRINKFILE (N'tempdev5' , 500)
DBCC SHRINKFILE (N'tempdev6' , 500)
DBCC SHRINKFILE (N'tempdev7' , 500)
DBCC SHRINKFILE (N'tempdev8' , 500)
GO
tempdevx is the fysicale file name
500 is the size after resize,
depends on the free space off course in the tempdb
February 22, 2011 at 7:15 am
i have just 1 tempdb mdf file not 1,2,3,4,5....
Regards
Sushant Kumar
MCTS,MCP
February 22, 2011 at 7:15 am
Tempdb contains internal objects such as spools and hashes used by the queries.
The space allocated by those objects cannot be reclaimed, even if not used.
To reclaim that space, in SQL 2005 the only option is running DBCC FREESYSTEMCACHE('ALL'), but I don't recommend it on a production database.
Hope this helps
Gianluca
-- Gianluca Sartori
February 22, 2011 at 7:22 am
SKYBVI (2/22/2011)
i have just 1 tempdb mdf file not 1,2,3,4,5....
then change it to you re needs remove the other files and only use 1 with youre specs and size.
Keep Gianluca comment in mind
February 22, 2011 at 7:25 am
Gianluca Sartori (2/22/2011)
Tempdb contains internal objects such as spools and hashes used by the queries.The space allocated by those objects cannot be reclaimed, even if not used.
To reclaim that space, in SQL 2005 the only option is running DBCC FREESYSTEMCACHE('ALL'), but I don't recommend it on a production database.
Hope this helps
Gianluca
That means the space once allocated to tempdb can never be re-used widout using tht dbcc command.
What happens to the space when we normally shirnk those files.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 22, 2011 at 7:29 am
Marco V (2/22/2011)
DBCC FREEPROCCACHEGO
Please note that DBCC FREEPROCCACHE flushes the procedure cache for the whole server. Depending on your database load, your server could suffer for some minutes due to recompilations. You could also consider running DBCC FLUSHPROCINDB(@dbid) instead, to reduce the scope of the cache flush.
After running DBCC FREEPROCCACHE, I also run sp_updatestats to ensure that the newly generated plans are not based on stale statistics.
-- Gianluca Sartori
February 22, 2011 at 7:31 am
SKYBVI (2/22/2011)
Gianluca Sartori (2/22/2011)
Tempdb contains internal objects such as spools and hashes used by the queries.The space allocated by those objects cannot be reclaimed, even if not used.
To reclaim that space, in SQL 2005 the only option is running DBCC FREESYSTEMCACHE('ALL'), but I don't recommend it on a production database.
Hope this helps
Gianluca
That means the space once allocated to tempdb can never be re-used widout using tht dbcc command.
What happens to the space when we normally shirnk those files.
Regards,
Sushant
Shrinking means releasing unused allocated space, with some exceptions. Tempdb internal objects are one of those exceptions: when you normally shrink tempdb, that space is not released.
-- Gianluca Sartori
February 22, 2011 at 7:34 am
Or you could just leave it at 10 Gig since it may need to grow again anyway.
Note the warnings and conditions, such as "...no activity occur in the tempdb database during the shrink operation...."
February 22, 2011 at 7:47 am
homebrew01 (2/22/2011)
Or you could just leave it at 10 Gig since it may need to grow again anyway.Note the warnings and conditions, such as "...no activity occur in the tempdb database during the shrink operation...."
It doesnt grow that much because it was initialzed at 10 gb, a long time ago and it is same after months.. and it has 90% free space..
Yes, i will run hte job at night time, wen there is least activity going around the db.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 23, 2011 at 8:34 am
DBCC FREEPROCCACHE
GO
USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb', 10, TRUNCATEONLY)
USE [tempdb]
GO
sp_updatestats;
I will be running it at night.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 24, 2011 at 4:49 am
@Sushant,
Ideally Shirnking is not recommanded since it can cause fragementation.
And shirnking on Tempdb, i will not go for this operation unless i am in dire need of HDD space.
February 24, 2011 at 5:19 am
I HIGHLY recommend you leave it at 10GB. That is pretty small given a 340GB production database, and also small in comparison to disk sizes these days. Do you have a REASON you want to shrink it other than "it has 90% free space"? That is NOT a valid reason. Tempdb usage is transient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2011 at 6:47 am
I also dont like shrinking the tempdb personally.
10gb is fine for a 340 gb prod db, i know that.
But my manager doesn't like that.
I told her many reasons for not shrinking it...but she cant understand..
🙁
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 24, 2011 at 6:53 am
SKYBVI (2/23/2011)
DBCC FREEPROCCACHEGO
USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb', 10, TRUNCATEONLY)
USE [tempdb]
GO
sp_updatestats;
I will be running it at night.
Regards,
Sushant
I tried this also, the job runs fine without any error, but the size of tempdb remains the same.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply