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

Shrinking Temp DB Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 8:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 321, Visits: 571
I am trying to shrink the first temp db mdf, but it is not shrinking... 95% free... just added some new mdf's.

Using this command:
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 17724)

I get the following:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
2 1 4029552 1024 10160 10160
Post #1500791
Posted Wednesday, October 2, 2013 10:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
..Whats the current size of the tempdb in MB ..?
And how much MB are you trying to shrink it to ?...
Post #1500845
Posted Thursday, October 3, 2013 2:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 15,664, Visits: 28,062
If the tempdb is actively in use, you may not be able to shrink it until the objects stored there are cleared out.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1501057
Posted Thursday, October 3, 2013 8:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
dwilliscp (10/2/2013)
I am trying to shrink the first temp db mdf, but it is not shrinking


IN two cases , size will not get reduced.

1) Shrink will not work on active transational part.
2) size will not get reduced below initial size.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1501231
Posted Friday, October 4, 2013 11:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 321, Visits: 571
Bhuvnesh (10/3/2013)
dwilliscp (10/2/2013)
I am trying to shrink the first temp db mdf, but it is not shrinking


IN two cases , size will not get reduced.

1) Shrink will not work on active transational part.
2) size will not get reduced below initial size.


Ah.. number 2 would explain it.. How do you get it to shrink below the size it was created with... Alter Database tempdb Modify File (name = temp1.mdf, size=100mb).. then wait for a reboot?
Post #1501700
Posted Friday, October 4, 2013 11:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Yup.

Generally it's not a good idea to shrink tempDB anyway. Unless the system is completely quiesced, shrinking TempDB can cause corruption that will require a restart to fix (there's a kb article on it)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1501703
Posted Friday, October 4, 2013 11:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 321, Visits: 571
Gail, I had to create extra MDF's to deal with SGAM contention, so now I need to shrink the old file.
Post #1501716
Posted Friday, October 4, 2013 12:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 PM
Points: 42,849, Visits: 35,978
Yes, I gathered as much. What I said stands.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1501726
Posted Tuesday, October 8, 2013 9:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:50 PM
Points: 550, Visits: 1,611
"Have you tried turning it off and on again?"

If you're able to schedule a downtime, do it. Simpley stopping the SQL Service and restarting will return the TempDB to its initially configured size. Before doing that make sure the configured size is large enough for normal operations and has an appropriate number of equally sized files.
Post #1502669
Posted Tuesday, October 8, 2013 5:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 321, Visits: 571
dan-572483 (10/8/2013)
"Have you tried turning it off and on again?"

If you're able to schedule a downtime, do it. Simpley stopping the SQL Service and restarting will return the TempDB to its initially configured size. Before doing that make sure the configured size is large enough for normal operations and has an appropriate number of equally sized files.


Yes, it created the file at the old... large .. size. I will make the change once we get to the monthly maintenance day.
Post #1502878
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse