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?

[Webinar] Streamline your web hosting managementRegister Today

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.