Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

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)
0
25112
Asked:
25112
  • 3
  • 2
3 Solutions
 
dbaSQLCommented:
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 PerkinsCommented:
You need to restart the SQL Server service and it will initialize tempdb to whatever setting you had configured.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 PerkinsCommented:
>>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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now