Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 TEMPDB Shrink

Author  Topic 

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-02-18 : 23:02:11


I am trying to purge the TEMPDB MDF and LDF files. In order to do that I have used Sp-Spaceused Stored procedure and found the DB SIZE and UNALLOCATED SIZE and in line below Reserved, DataSize and Index Size and Unused.

database_namedatabase_sizeunallocated space
tempdb 1029.25 MB 1026.48 MB

reserved dataindex_sizeunused
1296 KB 584 KB 608 KB 104 KB

From this I need to calculate the size accordingly to the percent.

How to calculate from here as I am doing this TEMPDB Shrinking for the first time.

Can anyone help me in figuring out the explanation? Thanks all.

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-18 : 23:32:52
seems pretty reasonable

Why?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 02:05:38
" I am doing this TEMPDB Shrinking "

Please do NOT do that. You may corrupt your databases.

There is no need to shrink TEMPDB - next time SQL restarts it will be recreated at its default size.

If it does NOT shrink by itself on restart then there is a very strict procedure to go through to shrink TEMPDB - which involves starting SQL in emergency mode and all sorts of tricky stuff!
Go to Top of Page

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-02-19 : 05:03:37
Thanks for the suggestions.

however, I have organised for SQL Restart but Only marginally with LDF got shrunk but not MDF.

Can you please explain this phenomenon.

Thanks once again.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 05:09:31
What is the setting on TEMPDB for the initial size?

You don't want to shrink it down to "nothing" - there is a cost each time it needs to be increased - both in CPU and in the fragmentation of the files which will occur.

However, sometimes it will grow huge because of a single, unusual, query and it makes sense that it is shrunk back to "normal maximum size" at the next restart.
Go to Top of Page

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-02-19 : 05:49:29
TEMPDB MDF is 8 MB and LDF 1 MB as their Initial sizes.

I am expecting to the closest of these sizes but after restart only LDF got only shrunk not MDF. May I expect how to get it reduced the MDF.

Thanks
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-19 : 08:08:10
Depending upon the Workload,You should have initial size for Database as frequent autogrow of TempDB Data files can kill performance.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-19 : 10:22:33
are we sayining 1GB is too big?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 10:42:41
Either way, it should have shrunk back to its configured init-size when SQL restarted, no?
Go to Top of Page

james_wells
Yak Posting Veteran

55 Posts

Posted - 2010-02-20 : 07:49:30



Shrink operations do not shrink the version store or internal objects.

This means that you will not recover space if the internal object or
version store page needs to be moved.

The DBA might need to restart the server in a single user mode to
allow shrinking of the tempdb



run this script

SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb,
SUM (User_object_reserved_page_count +
internal_object_reserved_page_count +
version_store_reserved_page_count +
unallocated_extent_page_count +
mixed_extent_page_count) * 8 as total_space
FROM sys.dm_db_file_space_usage
Go to Top of Page

james_wells
Yak Posting Veteran

55 Posts

Posted - 2010-02-20 : 07:49:32



Shrink operations do not shrink the version store or internal objects.

This means that you will not recover space if the internal object or
version store page needs to be moved.

The DBA might need to restart the server in a single user mode to
allow shrinking of the tempdb



run this script

SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb,
SUM (User_object_reserved_page_count +
internal_object_reserved_page_count +
version_store_reserved_page_count +
unallocated_extent_page_count +
mixed_extent_page_count) * 8 as total_space
FROM sys.dm_db_file_space_usage
Go to Top of Page

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-02-21 : 18:35:28
Thanks to all participated here.

In my Case We have simply Re-started the Server and it was successful in relinquishing the sizes to low.

But there after there was a transaction and TEMPDB has registered a sizeable growth. How to control this.

However, I have another flip to the same question as how and why TEMPDB can be prevented to be FULL. How can this be Controlled.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 01:10:50
" But there after there was a transaction and TEMPDB has registered a sizeable growth. How to control this"

This is normal. TEMPDB will grow to accommodate the largest mix of transactions that it encounters. You should explicitly size it to accommodate a "reasonable" size of such transactions - i.e. the size that you see TEMPDB grow to in "normal" conditions.

However, specific actions can cause large impact on TEMPDB. e.g. DBCC CHECKDB on a database with Indexed Views (I think). So you may want to check if TEMPDB grows suddenly at some particular time in the day, and then find out what transactions happened at that time - most probably it will be a scheduled task, which you could then "tune"

" how and why TEMPDB can be prevented to be FULL. How can this be Controlled"

Set TEMPDB to unlimited growth. Set the expansion size to a reasonable FIXED size (and not a percentage),. Make sure TEMPDB is on a disk that has adequate dis space - install will often put it on C: which may be your smallest drive.

You said earlier " after restart only LDF got only shrunk not MDF" has this now been fixed? How?
Go to Top of Page

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-02-22 : 18:15:29
As you have mentioned as a part of DBCC CHECKDB with Indexed Views there is no problem with TEMPDB to grow; but with a transaction running for couple of hours then the TEMPDB started to grow, I think the Sorting for the data for which the transaction was done caused to grow.

The TEMPDB LDF got shrunk to its lowest possible manner, but after relocating the TEMPDB to another drive then it got Shrunk on Rebooting.

If this is to happen everytime to shrink the TEMPDB (Both Files) is it require to RELOCATE the Tempdb's place?

Thanks again.

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 02:39:27
" I think the Sorting for the data for which the transaction was done caused to grow""

Yes, that is quite possible.

" If this is to happen everytime to shrink the TEMPDB (Both Files) is it require to RELOCATE the Tempdb's place"

No, restart SQL Server should be enough. If not there is some problem that needs to be found and fixed.

Might be worth checking if you have latest Service Pack, and if not whether there are any notes about your problem in latest service pack releases.

SELECT @@version

and check the number here:
http://www.sqlteam.com/article/sql-server-versions
Go to Top of Page

dbird
Starting Member

4 Posts

Posted - 2010-11-05 : 13:44:12
Why in the sample SQL the page count is be multiplied by 8.
example SUM (user_object_reserved_page_count)*8 as usr_obj_kb,

Multiple an 8k page by 8 does not provide the results in Kilobytes, the answer is in multiples of 64 KB.

For samples on converting it to Megabytes see the Microsoft documentation Troubleshooting Insufficient Disk Space in tempdb http://msdn.microsoft.com/en-us/library/ms176029(v=SQL.100).aspx

Go to Top of Page

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-11-07 : 21:29:10
Going by the Assumption that RESTART of SQL Server will automatically shrinks back to its orginal settings.

However, there is no evidence that SQL Server Restart is preferred by MS SQLServer.

Is there any article supporting SQL SERVER Restart for Troubleshooting the TEMPDB problem?

This might really help in the long run.

Thanks All for your wonderful participation.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-11-08 : 03:22:10
"there is no evidence that SQL Server Restart is preferred by MS SQLServer"

To be best of my memory Microsoft states that TEMPDB should only be shrunk in Single User Mode. That in itself requires a restart for single user mode, and a further restart to get back to normal.

Given that TEMPDB should revert to configured initial size on a restart then that only needs a single restart.

But we do get questions here where TEMPDB doesn't reconfigure back to initial size on re-start. Not sure there has ever been any conclusive evidence for "why", or what the fix was (other than the single-user method of Shrink), so there appears to be a rare and hard-to-diagnose problem with TEMPDB shrinking on re-start
Go to Top of Page
   

- Advertisement -