January 25, 2012 at 11:53 am
HI,
Tempdb data file size increased suddenly , intial size 5 MB , now size has increase 45 GB , How can i decrase the size of mdf file. please give solution. (This is prod server)
Thanks
Jery
January 25, 2012 at 11:59 am
You need to see how the tempdb space is being used.
Run these queries to check the current usage.
print 'Check usage of tempdb'
select
[Version Store Pages Used] =
sum(version_store_reserved_page_count),
[Version Store Space MB] =
convert(numeric(10,2),round(((sum(version_store_reserved_page_count)*1.0)/128.00),2)),
[User Object Pages Used] =
sum(user_object_reserved_page_count),
[User Object Space MB] =
convert(numeric(10,2),round(((sum(user_object_reserved_page_count)*1.0)/128.00),2)),
[Internal Object Pages Used] =
sum(internal_object_reserved_page_count),
[Internal Object Space MB] =
convert(numeric(10,2),round(((sum(internal_object_reserved_page_count)*1.0)/128.00),2)),
[Unallocated Pages Used] =
sum(unallocated_extent_page_count),
[Unallocated Space MB] =
convert(numeric(10,2),round(((sum(unallocated_extent_page_count)*1.0)/128.00),2)),
[Total Pages] =
sum(user_object_reserved_page_count)+
sum(internal_object_reserved_page_count)+
sum(version_store_reserved_page_count)+
sum(unallocated_extent_page_count),
[Total Space MB] =
convert(numeric(10,2),round(((
sum(user_object_reserved_page_count)+
sum(internal_object_reserved_page_count)+
sum(version_store_reserved_page_count)+
sum(unallocated_extent_page_count)*1.0)/128.00),2))
from
sys.dm_db_file_space_usage;
print 'Check % of tempdb database:'
select
[Version Store %]= convert(decimal(6,3), v*100.0/(u+i+v+f)),
[User Object %]= convert(decimal(6,3), u*100.0/(u+i+v+f)),
[Internal Object %]= convert(decimal(6,3), i*100.0/(u+i+v+f)),
[Free Space %]= convert(decimal(6,3), f*100.0/(u+i+v+f)),
[Total]= (u+i+v+f)
from
(
select
u = sum(user_object_reserved_page_count)*8,
i = sum(internal_object_reserved_page_count)*8,
v = sum(version_store_reserved_page_count)*8,
f = sum(unallocated_extent_page_count)*8
from sys.dm_db_file_space_usage
) x;
January 25, 2012 at 12:22 pm
Also, take a look at this article for a process to create in order to capture this information automatically in the future.
http://www.sqlservercentral.com/articles/Log+growth/69476/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 25, 2012 at 11:41 pm
was there any new indexes created or a Re index maintainence job that ran today?
Any other known long open transaction?
Regards,
Raj
January 26, 2012 at 8:18 am
Once you've done everything that has been suggested. You should also set the size of tempdb accordingly so it does not need to autogrow. You may find that your tempdb is used enough to justify a 45GB data file (files really should be). I think this is unlikely, but may be possible.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply