tempdev too big

Last Post 24 May 2011 12:54 AM by ctseah. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ctseah
New Member
New Member

--
10 May 2011 01:44 AM
Hi forumers,

I encountered a diskspace issue due to the tempdev datafile because too big around 40GB.

I tried to shrinkfile to a smaller size, but tempdev size remains the same. No specific error recorded while shrinking the file.

How to reduce it successfully without restarting the sql service.

Thanks in advance.

regards

rm
New Member
New Member

--
10 May 2011 04:45 AM
Any open transaction that uses tempdb? Need stop it before shrinking tempdb. By the way, do you use snapshot isolation? It consumes tempdb space too.
ctseah
New Member
New Member

--
10 May 2011 03:03 PM

When I use the sql studio management to shrinkfile the tempdev, the shrinkfile min size that it can be shrunk to is very small, but I can't shrink it despite that.
I am not sure whether there is any open transaction that used tempdb, but I doubt so, because I perform this shrinking during a weekend where no user.

I also read somewhere that if the tempdev was startup with a big initial size, then you can't shrink it to anything smaller than it original size. But I am not sure during startup what was original size either.

Any suggestion would be appreciate, thanks

Regards
rm
New Member
New Member

--
11 May 2011 04:40 AM
Do you use any row versioning related features on the server?
ctseah
New Member
New Member

--
11 May 2011 10:41 PM
Hi,

I don't think row versioning has been turned on.

Regards
rm
New Member
New Member

--
12 May 2011 05:45 AM
Looks like need restart sql.
gunneyk
New Member
New Member

--
14 May 2011 06:12 AM
I agree that it sounds like you have something still using data in tempdb and until that is removed it can't shrink pst a certain point. I would restart it as well.
ctseah
New Member
New Member

--
24 May 2011 12:54 AM
Hi ,

After restarting the sql service, I could shrink the file successfully.

Thank you very much for your advise.

Regards


Acceptable Use Policy
---