October 2, 2013 at 8:16 am
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:
DbIdFileIdCurrentSize MinimumSizeUsedPages EstimatedPages
214029552 10241016010160
October 2, 2013 at 10:29 am
..Whats the current size of the tempdb in MB ..?
And how much MB are you trying to shrink it to ?...
October 3, 2013 at 2:48 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 3, 2013 at 8:57 am
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;-)
October 4, 2013 at 11:13 am
Bhuvnesh (10/3/2013)
dwilliscp (10/2/2013)
I am trying to shrink the first temp db mdf, but it is not shrinkingIN 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?
October 4, 2013 at 11:21 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2013 at 11:46 am
Gail, I had to create extra MDF's to deal with SGAM contention, so now I need to shrink the old file.
October 4, 2013 at 12:37 pm
Yes, I gathered as much. What I said stands.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2013 at 9:01 am
"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.
October 8, 2013 at 5:16 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply