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: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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: Yesterday @ 7:16 PM
Points: 1,538, Visits: 2,587
..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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:27 PM
Points: 13,776, Visits: 28,178
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 Query Performance Tuning
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: Monday, September 29, 2014 5:01 AM
Points: 2,840, Visits: 3,968
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: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 39,983, Visits: 36,351
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: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 39,983, Visits: 36,351
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: Friday, September 26, 2014 3:00 PM
Points: 554, Visits: 1,621
"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: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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