February 17, 2012 at 2:32 am
Today I found TempDb size has increased and disk space is very low. I need to get more free space in disk. And I tried to shrink TempDb but it is not getting any free space in disk. So May i know that what is alternative step to take to get free space by without restarting SQL Server. Because it is in production environment the users are connected. My disk Used Space is 15.6 GB and Free Space is 4.29 GB
February 17, 2012 at 2:44 am
mohan.bndr (2/17/2012)
Today I found TempDb size has increased and disk space is very low. I need to get more free space in disk. And I tried to shrink TempDb but it is not getting any free space in disk. So May i know that what is alternative step to take to get free space by without restarting SQL Server. Because it is in production environment the users are connected. My disk Used Space is 15.6 GB and Free Space is 4.29 GB
Please execute below query & post the output here:
SELECT [name],[log_reuse_wait_desc] FROM sys.databases
WHERE [name]='tempdb'
February 17, 2012 at 2:48 am
ya, I got the result below...
namelog_reuse_wait_desc
tempdbNOTHING
February 17, 2012 at 2:55 am
first thing I would look at is what caused tempdb to grow, index rebuilds being done in tempdb, excessive sort operations, increased number of temp tables table variables etc.
shrinking the file will give you the space but it isn't identifiying root cause and could happen again.
February 17, 2012 at 2:58 am
But when I try to shrink, I will not get any free space in disk.
I executed the query below ...
DBCC SHRINKFILE ('tempdev', 1024)
eventhough I didn't get free space in disk.
February 17, 2012 at 3:03 am
http://support.microsoft.com/kb/307487
if you cant shrink the file it means something is using the space, find out what, kill the session drop the object etc then shrink the file
i wouldnt shrink tempdb though, i would get more space on the drive as i said in my first post it will happen again unless you stop what caused it to grow in the first place
February 17, 2012 at 3:06 am
mohan.bndr (2/17/2012)
But when I try to shrink, I will not get any free space in disk.I executed the query below ...
DBCC SHRINKFILE ('tempdev', 1024)
eventhough I didn't get free space in disk.
The query that I provided was to check why log can not be reused, well you are trying to shrink the data file. You must have got some temporary object created within tempdb which is still being referenced somewhere. That is why you are not able to shrink it.
Did you run any heavy query with sorting ???
February 17, 2012 at 3:15 am
No, I didn't run any heavy query with sorting in my side. may be the users are trying it.... so that case how can we find. Please let me know what should i do to get free space back to disk.
February 17, 2012 at 7:59 am
mohan.bndr (2/17/2012)
No, I didn't run any heavy query with sorting in my side. may be the users are trying it.... so that case how can we find. Please let me know what should i do to get free space back to disk.
Please run this query to identify the sessions that are utilizing most of your TempDB.
SELECT session_id,(user_objects_alloc_page_count*8/1024) AS SpaceUsedByTheSessionMB,*
FROMsys.dm_db_session_space_usage DDSSU
WHEREdatabase_id=DB_ID('tempdb')
ANDuser_objects_alloc_page_count > 0
If you find any session taking more than expected space, check the query that has been executed from that session_id.
DBCC INPUTBUFFER (session_id)
If the query execution had been completed successfully but somehow the session is still open then you can decide to kill the session or you may want to check the status of the session. Once you do it, you should be able to shrink the TempDB database.
February 17, 2012 at 8:00 am
mohan.bndr (2/17/2012)
Today I found TempDb size has increased and disk space is very low. I need to get more free space in disk. And I tried to shrink TempDb but it is not getting any free space in disk. So May i know that what is alternative step to take to get free space by without restarting SQL Server. Because it is in production environment the users are connected. My disk Used Space is 15.6 GB and Free Space is 4.29 GB
Mohan, this is a big issue you can't find it instantly what is causing the tempdb growth.
There are 3 kinds of process which use tempdb space internal storage, user storage and version storage. You have to run some dmv's to figure which one of the above is causing the tempdb growth.
Try to reboot the server for now in maintenance time when not many users are connected and work on the above issues.
May 21, 2012 at 12:28 am
sorry, it is in production environment. It will not allo any down time also.
May 22, 2012 at 2:22 am
use following script to query space used by objects, then figure out what happened.
----the tempdb space used by user and by internal objects
Select
SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2
June 18, 2012 at 6:29 pm
This will also work
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
June 20, 2012 at 12:30 pm
June 20, 2012 at 3:16 pm
You don't need to reboot the server as a last resort to shrink the tempDB. You can shut off & turn on the SQL Server service in Configuration Manager.
Yes, that does mean downtime, but not as much as a server reboot would.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply