unable to shrink tempdb data file in single user mode

select @@spid
union
select spid from sys.sysprocesses where dbid = 2

gives me only one row.. (the session is the only user in tempdb)

still the below does not shrink it. why? what could be blocking it, since there is no other spid that is keeping a hold on it?

dbcc shrinkfile (1,10240)
LVL 5
25112Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dbaSQLConnect With a Mentor Commented:
0
 
25112Author Commented:
in that link, Method 2 & 3 do not avail any help. i am hoping to avoid Method 1.

if I am the only spid, why would there any hindrance to shrink it?
0
 
Anthony PerkinsConnect With a Mentor Commented:
You need to restart the SQL Server service and it will initialize tempdb to whatever setting you had configured.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
25112Author Commented:
i had told management that i will have to restart sql service after off-hours..

but now, when i ran dbcc shrinkfile (1,10240) again, it worked fine.. (it shrank it).. could there be processes not seeing in sysprocesses that could be holding tempdb, and when it is released it works ok to shrink?
0
 
Anthony PerkinsConnect With a Mentor Commented:
>>could there be processes not seeing in sysprocesses that could be holding tempdb, and when it is released it works ok to shrink? <<
Yes.
0
 
25112Author Commented:
good to know..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.