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 spacetempdb 1029.25 MB 1026.48 MBreserved dataindex_sizeunused1296 KB 584 KB 608 KB 104 KBFrom 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 |
|
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! |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
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? |
|
|
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 tempdbrun this scriptSELECTSUM (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 |
|
|
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 tempdbrun this scriptSELECTSUM (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 |
|
|
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. |
|
|
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? |
|
|
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. |
|
|
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 @@versionand check the number here:http://www.sqlteam.com/article/sql-server-versions |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
|