February 21, 2012 at 5:02 am
Hi folks!
I've a database with about 163000 MB file size of data file and 36% free space.
I tried to shrink the data file using
DBCC SHRINKFILE (N'paris02_sl' , 100000)
- it runs 7 hours than I canceled it - size was always 163000 MB.
Next try:
DBCC SHRINKFILE (N'paris02_sl' , 160000) to reduce by 3000 MB
- it runs more than 2 hours and creates more than 70 GB I/O (about 9000000 pages)
- I canceled this one too.
Next try:
A loop with shrinking by 200 MB per step
- the first steps are fast (some seconds per step)
- then there are some throttelings and different patterns of I/O amount
My general question is - may this behaviour (especially the huge I/O amount) come from heap tables (tables with no clustered index) which have nonclustered indexes for which a lot of pointers need to be updated during the shrink operation..
...or any other ideas?
BTW: This is an one time emergency shrink - I'm familar with "no periodical shrinks at all"!
Regard from Germany
SeBaFlu
February 21, 2012 at 5:56 am
Heaps or maybe LOB columns in tables (they're a pain for the shrink operation to handle)
Shrink in chunks sometimes works better than one huge (and certainly gives at least the impression it's faster)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply