question

David Wimbush avatar image
David Wimbush asked

Shrink file suspended for hours

UPDATE: Please ignore this one. The shrink finally finished and now I have a different problem. The data file is still 22GB but the shrink dialogue reckons it has -495% free space. I can feel a service restart coming on!

We had some problems overnight and my tempdb data file went up to 22GB. It's usually about 250MB. It was only using about 170MB so I used the GUI to shrink it back to 250MB. It's been stuck on 'Executing' for hours now. Yikes!

Activity Monitor shows it's suspended with a wait type of SLEEP_LOCK which seems to mean 'no specific reason'. No blocking or blocked by SPIDs. The actual SQL is dbcc shrinkfile (N'database-name', 250). sys.dm_exec_requests says it's just over 1% complete. It looks like it might be getting little slices of work from SQL because it has a last_wait_type of SLEEP_TASK and the wait_time is about 20% of the wait time in the Activity Monitor.

Could it be stuck because we're using read committed snapshot in our user databases and it can't clear something because a row version is still in use?

It's not causing any noticeable problems on the server.

Any ideas, please? Can I just kill the SPID?

sql-server-2005shrink-database
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

Kaalkop avatar image
Kaalkop answered

You should be able to just kill the spid, but no guarantees on that. Normally the tempdb will shrink itself when the instance of SQL is restarted.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
Dankie, Kaalkop. Dit is my production server. Ek sal wag 'n bietjie voor ek die SPID moord! (Ek het 10 jaar by Kaapstad gewoon.)
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

I'm sure it's too late, but don't restart the system. Try running sp_spaceused first.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
Not too late thanks, Grant. We're in the middle of the business day and the server's performing OK so a restart isn't an option. I ran sp_spaceused @updateusage = 'true' and it confirms what the Disk Usage report shows: 22GB & over 98% unallocated. But the shrink dialogue still shows the negative free space. Did I miss what you had in mind?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Nope. That's what I was thinking.... hmmm. I haven't seen that one before. You might want to open a Connect entry.
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

I have frequently seen that negative percentage with the tempdb data files. Typically it was when the tempdb was on a san (haven't seen it for a long time now though). I think another piece of that puzzle was multiple tempdb data files.

I would try to do a shrinkfile on it instead of shrink database.

Oh, and check out my article "Log Growing Pains" at SSC on tracking the cause of the growth. It's too late this time, but you could use it for future reference.

http://www.sqlservercentral.com/articles/Log+growth/69476/

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.